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.