Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    2

    Unanswered: Left Join / Where clause

    I have a problem, I have 2 tables. We'll call the first table Items, and the second table ItemOptions. Each item can "have" more then one item option, so I link each option record to the primary key of the items table...

    Tables

    "Items"
    -----------------
    itm_id
    name

    "ItemOptions"
    -----------------
    opt_id
    itm_id
    material

    Now, If I wanted to return all items and all the Options (whether or not the Item has options) I would use the following query..

    SELECT Items.*, ItemOptions.*
    FROM Items LEFT JOIN Options ON Items.id = Options.itm_id;

    Now, say that I wanted to select itm_id = 3 (the floor) and with that item only show the Options that have a material "wood" (where material = wood). This would obviously return no results, but how do I get the query to at least return the content of the "Items" table..

    So for example: Table Data:

    "Items"
    ------------------------
    itm_id | name
    ------------------------
    1 Desk
    2 Chair
    3 Floor

    "ItemOptions"
    -----------------------------------
    opt_id | itm_id | material
    -----------------------------------
    1 1 Wood
    2 1 Glass
    3 2 Wood
    4 3 Brick

    Selecting all available rows, How would I get the output of the query to be

    ----------------------------------------------------------
    itm_id | name | opt_it | itm_id | material
    ----------------------------------------------------------
    3 Floor null null null

    I have tried a left join, but because of my where clause of material = wood the query comes up with no results.

    Thank you,
    - Dave

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    maybe this would work?

    Code:
    create table #Items(itm_id int identity(1,1), [name] varchar(15))
    create table #ItemOptions(opt_id int identity(1,1), itm_id int, material varchar(10))
    
    insert into #Items ([name]) values('Desk')
    insert into #Items ([name]) values('Chair')
    insert into #Items ([name]) values('Floor')
    
    insert into #ItemOptions(itm_id, material) values(1, 'Wood')
    insert into #ItemOptions(itm_id, material) values(1, 'Glass')
    insert into #ItemOptions(itm_id, material) values(2, 'Wood')
    insert into #ItemOptions(itm_id, material) values(3, 'Brick')
    
    select *
      from #Items i
      join #ItemOptions ios on i.itm_id = ios.itm_id
     where ios.Material = 'Wood'
    union
    select i.*, null,null,null
      from #Items i
      left join #ItemOptions ios on i.itm_id = ios.itm_id
     where ios.Material != 'Wood'
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jul 2003
    Posts
    2
    Thank you very much for your reply paul. I ended up fixing the problem with the following query:

    Code:
    SELECT items.item_id, items.item_name, itemoptions.item_id, itemoptions.itemoption_id, itemoptions.material 
       FROM items LEFT JOIN itemoptions
            ON (items.item_id = itemoptions.item_id AND itemoptions.material = 'wood')
    Thanks again, I appreciate your response.
    - Dave

Posting Permissions

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