join with multiple tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

join with multiple tables

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

union
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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)
Unipus
Forum Contributor
Posts: 409
Joined: Tue Aug 26, 2003 2:06 pm
Location: Los Angeles, CA

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Post Reply