Joining table a to table b twice?

Databases
1

Hi all!

I came across this problem earlier, and have been banging my head against the wall for what ‘seems’ like should be a relatively simple issue. Actually kinda amazed I haven’t come across this before.

I have two tables ‘colors’ and ‘products’.

The products table has a field for product_color, which relates to the colors table.

Here is a sample query


SELECT 
    products.*
  , colors.hex

INNER JOIN colors
 ON colors.id = products.product_color_id

WHERE products.product_id = '123'

Well, all was well and happy until I was asked to add another color field in the products table.

So… each product will now have two fields that relate to the colors table and I need to join the hex value for both of them… I’ve tried multiple things and nothing seems to give me what I need. I’m open to ideas suggestions anything. Thanks in advance for any guidance.

Hopefully this will illustrate what i’m ‘trying’ to do.



SELECT 
    products.*
  , colors.hex as product_color
  , colors.hex as bag_color_hex

INNER JOIN colors
 ON colors.id = products.product_color_id

INNER JOIN colors
 ON colors.id = products.bag_color_id

WHERE products.product_id = '123'
2

Your current query looks for colors in the color table where the id the same as product_color in the product table and the same as the bag_color_id.

You need to give the color table two different aliases, like so:

SELECT 
    products.some_fields_but_not_the_dreaded_star
  , c1.hex as product_color
  , c2.hex as bag_color_hex
 INNER
  JOIN colors c1
    ON c1.id = products.product_color_id
 INNER
  JOIN colors c2
    ON c2.id = products.bag_color_id
 WHERE
   products.product_id = '123'

BTW. Either rename product_color to product_color_hex, or bag_color_hex to bag_color. You’ll thank me later :slight_smile: