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

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 )";