Page 1 of 1

join with multiple tables

Posted: Tue Jun 15, 2004 12:45 pm
by Unipus
... or maybe I've got this all wrong in my head. Anyway, I have a query currently that's basically a browse function. It takes the 'results' table (a filtered list of ItemNumbers) and matches it to the 'items' table (which has information on the items) on ItemNumber = ItemNumber. Okay, great. But now I need to join this query as a many-to-one with the 'rebates' table, which also has ItemNumber. But I want to return all results from 'results' regardless of if there's no match on rebates. I know how to do this in two queries but there must be a way to do it in one.

Posted: Tue Jun 15, 2004 2:02 pm
by feyd
union

Posted: Tue Jun 15, 2004 4:33 pm
by Weirdan
perhaps union is not what you're looking for.
If I understand you correctly, left join would serve better:

Code: Select all

select 
   results.*,
   items.*,
   rebates.*
from
   results
inner join
   items
using (ItemNumber)
left join 
   rebates
using (ItemNumber)

Posted: Tue Jun 15, 2004 8:33 pm
by Unipus
Yes, thanks, that's more like it. Union is only for use in the same table, or tables with identical columns, as I understand it?

Posted: Tue Jun 15, 2004 8:35 pm
by feyd
I've used union across many differing tables....atleast I think I have...hmm..

[edit] I must be on crack.. I was thinking of something else.. ignore the idiot in the corner.


as you were.