Re: Subject: union/select statement & number of columns

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



At 19:31 19/10/2007, you wrote:
Message-ID: <38.49.57393.F9BC8174@xxxxxxxxxxxx>
Date: Fri, 19 Oct 2007 09:24:38 -0600
Subject: union/select statement & number of columns

Hello all, I receive an error of the following: The used SELECT
statements have a different number of columns. Any help, pointers,
tutorials are appreciated.



That's *completely* wrong SQL for what you're trying to do. Reading between the lines (you didn't say what you *really* want to do), you seem to want one order_item row and the details about its order for each order item. Your query needs to be :

SELECT orders.*, order_items.*
FROM orders LEFT JOIN order_items
ON orders.order_id = order_items.order_id


UNION is completely the wrong thing here - it can only compare identical things, you're trying to join together two different data columns (order, and order_items details)

HTH
Cheers - Neil


Here are the two tables structure I am trying to pull from:
Table 1
mysql> describe orders;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(255)     | NO   | PRI |         | auto_increment |
| ordernum   | int(10)      | NO   |     |         |                |
| date       | varchar(60)  | NO   |     |         |                |
| time       | varchar(20)  | NO   |     |         |                |
| group      | varchar(20)  | NO   |     |         |                |
| purpose    | varchar(255) | NO   |     |         |                |
| tracking   | varchar(120) | NO   |     |         |                |
| contact    | varchar(255) | NO   |     |         |                |
| eta        | varchar(50)  | NO   |     |         |                |
| department | varchar(125) | NO   |     |         |                |
| notes      | varchar(255) | NO   |     |         |                |
+------------+--------------+------+-----+---------+----------------+
11 rows in set (0.01 sec)

Table 2
mysql> describe order_items;
+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
| id          | int(11)       | NO   | PRI |         | auto_increment |
| ordernum    | int(124)      | NO   |     |         |                |
| quantity    | int(124)      | NO   |     |         |                |
| description | varchar(124)  | NO   |     |         |                |
| price       | decimal(10,0) | NO   |     |         |                |
| partnum     | varchar(255)  | NO   |     |         |                |
| vendor      | varchar(255)  | NO   |     |         |                |
+-------------+---------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

And here is the statement I am using (PHP):
$query = "( SELECT * FROM `orders` WHERE ( `ordernum` LIKE "$var\" OR
`purpose` LIKE \"$var\" OR `tracking` LIKE \"$var\" OR `contact` LIKE
\"$var\" OR `date` LIKE \"$var\" OR `time` LIKE \"$var\" OR `eta` LIKE
\"$var\" OR `department` LIKE \"$var\" OR `notes` LIKE \"$var\" ) AND
`group` = \"$group\" ) UNION ( SELECT * FROM `order_items` WHERE (
`ordernum` LIKE \"$var\" OR `price` LIKE \"$var\" OR `partnum` LIKE
\"$var\" OR `vendor` LIKE \"$var\" OR `quantity` LIKE \"$var\" OR
`description` LIKE \"$var\" ) ORDER BY `ordernum` )";

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux