Union in inner join 1

[fordtran]

I am joining two tables (ORDEREDITEMS having a list of ordered items and an ID reference to items in another table named ITEMS which have the names of these ordered items.
I then do an inner join like in .. Inner join ITEMS on ORDEREDITEMS.ID=ITEMS.ID
This only sends me records of ordered items in which this reference to the ITEMS table does contain a name.
I also wish to add to these records the ordered items which does not contain a reference to a valid item like in :
Take all the records from ORDEREDITEMS which have ITEM.ID=null or 0 and add all the records which have a valid reference in the ITEMS table and join the names to these second set of records.

I am trying like this :

SELECT ordereditems.* FROM ordereditems where id=0 UNION ALL SELECT ordereditems.*, items.itemname FROM ordereditems INNER JOIN items on ordereditems.id=items.id

Thanks



fordtran

 

[Golom]

Rather than a UNION, try a LEFT JOIN.

SELECT ordereditems.*, items.itemname 

FROM ordereditems LEFT JOIN items on ordereditems.id=items.id

 

[lespaul]

additionally, when you are using a union you have to have the same number of fields in your SELECT clause, you have one more field in the second query than in the first, therefore this union wouldn't work anyway.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins