advanced join

[radox]

I'm racking my brain trying to figure out how to join 4 different tables. Please help!

 

Here's my tables:

 

auto_year

---------------

year_id

model_id

part_id

year

 

auto_model

---------------

model_id

make_id

model

 

auto_make

---------------

make_id

make

 

auto_part

---------------

part_id

part

 

I would like the output to be: year, make, model, part

 

You can see that:

auto_year and auto_part can be joined using part_id

auto_model and auto_make can be joined using make_id

 

I can't figure out how to tie these together using model_id

 

Hope I'm making sense. Maybe I'm missing something and there's a better way??

Many thanks!!

[ejaboneta]

Maybe you are making it too complicated. Are you trying to make a database of parts? If you are, you can make one table called 'auto_parts', and have have id, part, make, model, and year as columns.

[radox]

I appreciate your input, but I'm dealing with existing tables from a client's db. I need to solve the problem that I described. Thanks.

[ejaboneta]

Ok, well can you give the query you are currently using? what are you trying to do exactly? Are you trying to get a list of parts with details about which make, model and year they belong to?

[radox]

I got it:

 

select y.year, a.make, m.model, p.part 
from auto_year  y
join auto_model m using(model_id)
join auto_make  a using(make_id)
join auto_part  p using(part_id);

 

Thanks!