Join table 1 or join table 2 depending question

Databases
1

So I have a generic parts table:

GENERIC_PARTS
Fields: ID, NAME

And I have two tables of parts that could pulled from. The first table is all the parts with their relationship to the generic_parts table:

PARTS
Fields: ID, NAME, PRICE, GENERIC_ID, IS_DEFAULT

So I join those two and with a WHERE statement I can get the default Part for a specific GENERIC_PART record.

The problem is that users have the ability to create their own parts that will override the PARTS tables default part for the Generic item. So:

USER_PARTS
Fields: ID, NAME, PRICE, GENERIC_ID, USER_ID

How do I, in one statement, check the USER_PARTS table for a part first before using the PARTS table for the GENERIC items part?

I could combine the PARTS AND USER_PARTS with the regular parts having no USER_ID value and the USER_PARTS having the USER_ID value if that helps.

2 
SELECT generic_parts.name
     , COALESCE(user_parts.name
            ,generic_parts.name) as part_name
     , COALESCE(user_parts.price
            ,generic_parts.price) as price
  FROM generic_parts
INNER
  JOIN parts
    ON parts.generic_id = generic_parts.id
LEFT OUTER
  JOIN user_parts
    ON user_parts.generic_id = generic_parts.id
 WHERE generic.id = 937

:cool:

3

Right direction. The query needed two things though. First is a restriction to show default parts only and the second is to GROUP BY the generic_parts id. So:


Code:

SELECT generic_parts.name
     , COALESCE(user_parts.name
            ,generic_parts.name) as part_name
     , COALESCE(user_parts.price
            ,generic_parts.price) as price
  FROM generic_parts
INNER
  JOIN parts
    ON parts.generic_id = generic_parts.id
LEFT OUTER
  JOIN user_parts
    ON user_parts.generic_id = generic_parts.id
 WHERE generic.id = 937 AND parts.is_default = 1
 GROUP BY generic_parts.id

Thanks. That put me in the right direction. Just needed tweaking. Thank you so much!

4

the GROUP BY is not necessary

5

Oh…right. :wink: