SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict next2heaven's Avatar
    Join Date
    Apr 2005
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Join table 1 or join table 2 depending question...

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict next2heaven's Avatar
    Join Date
    Apr 2005
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the GROUP BY is not necessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict next2heaven's Avatar
    Join Date
    Apr 2005
    Posts
    257
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh...right. ;-)


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
  •