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

Good man. That' probably what he really wanted to do. On 10/21/07, Neil Smith [MVP, Digital media] <php@comatose.freeserve.co.uk> wrote: > > 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 ¤ð™Ý > > «‡}¼4sœŽ}¨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 > >