0

I have two tables

**Item**
ID
...

**ShapeItem**
ID
ItemID
ShapeID
RegionID

(There are other columns and tables but they are not relevant to this question)

I can return all items that have a specific shape id in a specific region id successfully using an INNER JOIN 

SELECT ID FROM Item INNER JOIN ShapeItem ON Item.ID = ShapeItem.ItemID WHERE ShapeID = 2

However I want to reverse this logic and return all items that DONT have a specific shape so my first thought was to do

SELECT ID FROM Item INNER JOIN ShapeItem ON Item.ID = ShapeItem.ItemID WHERE ShapeID <> 2

however this did not produce the required result, this returned all items that had a shape that was not the specific shape, but it did not account for those items that did not have any shapes at all.

My next thought was to use a LEFT JOIN but this returned every item with null values, (over 400,000)

I am currently stuck on this, can you suggest a way forward for me please?

Summary

I want to return all items that dont have a specific shape, including those items that are not referenced at all in the ShapeItem table.

  • SQL SERVER COMPACT 4.0
  • C#
  • Visual Studio 2012
Steven Wood
  • 2,675
  • 3
  • 26
  • 51
  • Quick suggestion : look into some kind of `SELECT ... WHERE NOT EXISTS (SELECT -- a subquery that would find any matches)` – AjV Jsy Sep 01 '14 at 11:59
  • ... if this doesn't help : http://stackoverflow.com/questions/4560471/how-to-exclude-rows-that-dont-join-with-another-table – AjV Jsy Sep 01 '14 at 12:06

0 Answers0