[SOLVED] Foreign keys question

[frikus]

I have two simple tables with the following fields:

 

1. 'items' : item_id, item_name, external_color_id, internal_color_id

2. 'colors' : color_id, color_name

 

external_color_id and internal_color_id are foreign keys to the 'colors' table.

 

How can I get both external and internal color names of the item with only one query?

 

I came up with the following query but I know that it's wrong:

 

SELECT items.*, colors.color_name AS c1, colors.color_name AS c2
FROM items, colors
WHERE items.item_id = 100
AND items.external_color_id = colors.id 
AND items.internal_color_id = colors.id 

[fenway]

You need to join the colors table twice (untested):

 

SELECT i.*, ci.color_name AS c1, ce.color_name AS c2
FROM items AS i
INNER JOIN colors AS ci ON ( i.internal_color_id = ci.id  )
INNER JOIN colors AS ce ON ( i.external_color_id = ce.id  )
WHERE i.item_id = 100

[frikus]

You are using two similar tables for colors: 'ci' and 'ce' in your query. Is it the optimal way?

 

Is there a way to use only one 'colors' table in double JOIN?

[frikus]

Please disregard my last post. You are absolutely right. Thanks.