[SOLVED] union help

[sasori]

let's say i have these 3 tables containing these data

 

customer table

`cust_id`, `fname`, `lname`, `street`, `city`, `state`, `zip`, `phone`, `fax`, `email`) 
(1, 'jason', 'vorjis', 'elm street', 'angels', 'CA', '20562', '000-000-0000', 'none', 'jvorjis@killer.'),
(2, 'freddy', 'kruger', 'elm street', 'angels', 'CA', '20204', '000-000-000', '', '[email protected]'),
(3, 'albert', 'einstein', 'geek street', 'carlson', 'MA', '23251', '000-000-000', '', 'aeinstein@test.'),
(4, 'jena', 'malone', 'sexy street', ' lake tahoe', 'NV', '223523', ' 123-456', '7890', ''),
(5, 'britney', 'bitch', NULL, 'indianapolis', NULL, NULL, '845-155-815', '1-800-800-188', NULL),
(6, 'meredith', 'brooks', 'bitch street', 'angels', 'TX', NULL, NULL, NULL, NULL),
(7, 'tom', 'jones', 'oldman street', 'pines', 'TX', NULL, NULL, NULL, NULL);

 

item table

(`order_id`, `item_id`, `color`, `size`, `price`) 
(1, 1, 'blue', 'medium', '15.00'),
(2, 2, 'red', 'large', '14.75');

 

item_order table

(`cust_id`, `order_id`, `date`) 
(5, 1, '0000-00-00 00:00:00');

 

how am i gonna print the output in the screen using the "UNION"  if i want the cust_id,fname,lame,order_id,color,size,price and date all at once ?  ???

[toplay]

Don't use a union and just join the tables. The way to do it depends on what data you want back. This is an example where it only displays info only on the customers that have ordered (it goes through the item_order table as the primary source).

 

select

        c.cust_id, c.fname, c.lname, i.order_id, i.color, i.size, i.price, io.date

from item_order io

join item i using (order_id)

join customer c using (cust_id)

order by io.cust_id, io.order_id, i.item_id

;

 

 

 

[sasori]

Don't use a union and just join the tables. The way to do it depends on what data you want back. This is an example where it only displays info only on the customers that have ordered (it goes through the item_order table as the primary source).

 

select

        c.cust_id, c.fname, c.lname, i.order_id, i.color, i.size, i.price, io.date

from item_order io

join item i using (order_id)

join customer c using (cust_id)

order by io.cust_id, io.order_id, i.item_id

;

where did that c, and i and io came from?  i got confused with your way of query

am just new to mysql..yours looks so advance ???

[toplay]

Look closely after the table names. Each one is assigned an alias 'io', 'i', and 'c'.

 

FYI:

http://sqlzoo.net