| Neil Smith [MVP, Digital media] 2007-10-21, 7:02 pm |
| At 19:31 19/10/2007, you wrote:
>Message-ID: <38.49.57393.F9BC8174@pb1.pair.com>
>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` )";
|