Can't figure out this left join SQL query

[ITdev]

Hi, I have been beating my brains on this query and can't get it to work.

tblItem has the field with id as the primary key
id name
1 A
2 B
3 C

tblCustom has the field (both field are foreign keys)
itemid sideid
1 15
1 17
2 15
3 16

tblSide has the field with the id as the primary key
id name
15 AA
16 BB
17 CC

What I'm trying to do is to select all the fields in tblSide where the itemid in tblCustom doesn't equal to 1.
The result I want for tblSide is this:
id name
16 BB

What I'm getting instead is
id name
15 AA
16 BB

the reason why 15 is showing up is because in tblCustom, 2 has 15, it shouldn't show up because 1 has value 15 too. Here's my SQL statement.

SELECT tblSide.id, tblside.name tblcustom.itemid, tblcustom.sideid
FROM tblside LEFT JOIN tblcustom on tblside.id = tblcustom.sideid
WHERE tblcustom.itemid <> 1 or tblcustom.itemid is null;

does anyone know what I am doing wrong? Thanks in advance for your input.

 

[OldTom]

Try this

SELECT DISTINCT tblSide.Id, tblSide.Name
FROM tblSide LEFT JOIN (tblItem RIGHT JOIN tblCustom ON tblItem.Id = tblCustom.ItemId) ON tblSide.Id = tblCustom.SideId
WHERE (((tblCustom.ItemId)<>1));

Tom

 

[r937]

the reason 15 is showing up is the way you have stated your problem -- &quot;select all the fields in tblSide where the itemid in tblCustom doesn't equal to 1&quot;

what you want is slightly differtent -- &quot;select all the fields in tblSide where the itemid in tblCustom equal to 1 doesn't exist&quot;

you were on the right track, it's a simple left join

what you have to do is search for unmatched rows, i.e. where the fields from the second table are null

select tblSide.name
  from tblSide
left outer
  join tblCustom
    on tblSide.id = tblCustom.sideid
   and              tblCustom.itemid = 1  
 where tblCustom.itemid is null

that might seem a bit counter-intuitive, it seems to be testing itemid = 1 and also itemid is null, but what it's telling the database is pretty straightforward -- do the join based on these conditions, and pick the rows where you didn't find a match

this type of problem can also be done with a NOT EXISTS subquery, which may be a bit easier to understand

select tblSide.name
  from tblSide
 where not exists
       ( select *
           from tblCustom
          where sideid = tblSide.id 
            and itemid = 1 )

rudy

http://rudy.ca/