Re: union/select statement & number of columns

For starters, here's a simple example using Oracle: select tname, tabtype from tab union select table_name, null from user_tables; I replaced NULL in the second query for the missing column. In a different observation, it would be better, I think, to name the individual columns rather than select *. Otherwise, any new columns added to either table (with, or without using UNIONs), may break the application if it's not prepared to handled unknown columns. On 10/19/07, Jas <jason.gerfen@scl.utah.edu> wrote: > > 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. > > 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 > >