SitePoint Sponsor

User Tag List

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

    Innie or Outie? JOIN that is.

    I wrestled with this query this weekend without much success. Any guidance would be greatly appreciated.


    table_list
    id | name
    1 | spec name one
    2 | spec name two
    3 | spec name three


    table_specs
    id | list_item_id | inventory_item_id | value

    1 | 2 | 181 | 100 gal


    I need two different queries.

    Query 1: return the entire table_list (all entries) Joined with table_specs ON table_specs.list_item_id = table_list.id AND table_specs.inventory_item_id = (whatever inventory item I'm looking at). This would give me a complete list of all possible specs but with some having empty values.

    Query 2: return ONLY the table_list items that have corresponding Joined values in the table_specs table for a certain inventory item.


    I've tried INNER JOIN, and several variations of OUTER's, at this point I've confused myself greatly.... sigh. Any guidance would be greatly appreciated.


    Thanks in advance for any guidance or suggestions!

  2. #2
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the code as it stands. This only returns a list that has values in the table_specs.

    Code:
    SELECT 
           table_list.id
         , table_list.name
         , table_specs.id AS unique_spec_value
         , table_specs.list_item_id 
         , table_specs.inventory_item_id
         , table_specs.value
    
    FROM table_list
    
    LEFT OUTER JOIN table_specs
        ON table_list.id = table_specs.list_item_id
    
    WHERE table_specs.inventory_item_id = 199
    btw, my copy of Rudy's book was at home this weekend and I was at my girlfriends place all weekend so normally I would reference it for this type of problem.

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    SELECT 
           table_list.id
         , table_list.name
         , table_specs.id AS unique_spec_value
         , table_specs.list_item_id 
         , table_specs.inventory_item_id
         , table_specs.value
    
    FROM table_list
    
    LEFT OUTER JOIN table_specs
        ON table_list.id = table_specs.list_item_id
    
    and table_specs.inventory_item_id = 199

  4. #4
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    AWESOME! That did it. For those of you that can't pick out the change,

    The WHERE was changed to an AND

    Thanks!!

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,706
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you do an OUTER JOIN and place a condition on the right hand table, that condition belongs in the join itself. Putting it in the where clause like you did filtered out the non matching rows, essentially creating an INNER JOIN.

    Thought you'd want to know that for future reference.

  6. #6
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup, it all made sense right when I saw the AND. Had one of those 'why didn't I think of that' moments.


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
  •