There is a table Shops with Shop_number and Shop Address columns. Also a table called Properties with two columns:

A shop can have more than one property. How to write a query which would return all shop numbers which does not have Property_ID: 113 at all (excluding 222222, because it indeed has other properties, but also 113).

SELECT p.shop_number FROM Properties p
WHERE p.property_id != 113

My query returns also store 222222 which has 113 property_id. I would like to return shop numbers: 222225 and 222226 in this case only.

CodePudding user response：

Either use not exists as @Larnu suggests or use group by / having:

select shopnumber
from t
group by shopnumber
having count(case property_id when 113 then 1 end) = 0;

case maps property_id = 113 to 1 and everything else to null. count(x) does not count rows where x is null.

CodePudding user response：

Your description is a bit unclear.

Since you already got an answer how to get your result in case you have to use one of the two tables only, let's have a look on your requirements again which can be read as you need both tables.

You are writing: "A shop can have more than one property. How to write a query which would return all shop numbers which does not have Property_ID: 113 at all"

I don't know if this is your intention, but according to your description, you also want to get all shops that don't even occur at all in the properties table.

So we could use such a query:

SELECT s.shop_number
FROM shops s
WHERE NOT EXISTS 
(SELECT 1 FROM properties WHERE property_id = 113 
AND shop_number=s.shop_number);

This will show all shop numbers that don't appear at all in the properties table and also all shop numbers that appear having properties different from 113 only.

Only those shop_numbers that occur in the properties table and exist there having the property id 113 will be excluded.

And this is exactly what you described as being your requirement. It's the question if what you told us you want to do is really what you want to do ;)