Here\'s the original post:
I have two tables that I need to get information from.
The AllStores table has a field called Stores with data in it like:
-Store 1 -Store 2 -Store 3 -etc. (unique store names)The AllDepartments table has two fields called Store and Department with rows like:
Store Department Store 1 Bakery Store 1 Produce Store 2 Bakery Store 2 Meat Store 3 Bakery Store 3 MeatNotice that Store 1 has a Produce dept. and no other store does.
What I need the query to do is:
Find all stores that don\'t have a Produce department in them.Which, in this case would be Store 2 and Store 3.
The solution in one mysql statement is:
The basic trick is to duplicate one of the tables in order to get rid of the record having the value you want to exclude...
SELECT DISTINCT Stores FROM AllStores A, AllDepartments D1, AllDepartments D2 WHERE A.Stores = D1.Store AND A.Stores <> D2.Store AND D2.Deparment = \'Produce\'
This will return all stores from A where the store does NOT have a Department called \'Produce\' .
(If people think this was easy and logical, then ignore this post).