SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Joining table a to table b twice?

    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

    Code:
    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.

    Code:
    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. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,895
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by rustybuddy View Post
    Code:
    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'
    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:

    Code MySQL:
    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
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •