Results 1 to 2 of 2
Nov 18, 2010, 16:16 #1
- Join Date
- Sep 2006
- 0 Post(s)
- 0 Thread(s)
Joining table a to table b twice?
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'
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'
Nov 18, 2010, 17:17 #2
- Join Date
- Aug 2008
- The Netherlands
- 153 Post(s)
- 2 Thread(s)
You need to give the color table two different aliases, like so:
BTW. Either rename product_color to product_color_hex, or bag_color_hex to bag_color. You'll thank me laterRémon - Hosting Advisor
SitePoint forums will switch to Discourse soon! Make sure you're ready for it!
Minimal Bookmarks Tree
My Google Chrome extension: browsing bookmarks made easy