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
