MySQL SUBSELETCS or something equivalent...

[pallevillesen]

A while ago somebody posted a question about how to return values from one table, but with some row excluded based on the precense in another table.

 

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                Meat

Notice 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).

 

Greetings,

P., denmark

[effigy]

bio this works great for the intended example, but it falters if oddities are produced, were you aware of this? if i run...

 

insert into alldepartments values (\'Store 4\', \'Produce\');

insert into alldepartments values (\'Store 4\', \'Cheese\');

insert into alldepartments values (\'Store 4\', \'Meat\');

 

...and run the query, it returns

 

Store 2

Store 3

Store 1

Store 4

 

i\'m not a master at sql, i hope i\'m not missing the obvious

[pallevillesen]

bio this works great for the intended example, but it falters if oddities are produced, were you aware of this? if i run...

 

insert into alldepartments values (\'Store 4\', \'Produce\');

insert into alldepartments values (\'Store 4\', \'Cheese\');

insert into alldepartments values (\'Store 4\', \'Meat\');

 

...and run the query, it returns

 

Store 2

Store 3

Store 1

Store 4

 

i\'m not a master at sql, i hope i\'m not missing the obvious

 

AAAARRRRRRGHHHH!!!!! Ok., I guess it\'s time for actually creating the database. I will return since I\'m absolutely positive that this will work... somehow... And it\'s friday anyway!

 

P.

[pallevillesen]

Ok, so now it\'s working. I\'m using a temporary table which I consider cheating - will try on combining JOINS instead:

 

(UPDATE: tried this - I do NOT think it is possible doing this without this temp. table).

 

test.sql:

 

USE test;

CREATE TEMPORARY TABLE exclude AS (SELECT * FROM departments WHERE

department = \'Produce\');

SELECT * FROM stores s LEFT JOIN exclude e ON s.id=e.id WHERE e.id IS

NULL;

DROP TABLE exclude;

 

and just to prove:

 

serine:~/bin% 

serine:~/bin% mysql -h aspargine -pXXXXX -e\'source ~/test.sql\'

+----+--------+------+------------+

| id | store  | id   | department |

+----+--------+------+------------+

|  2 | store2 | NULL | NULL       |

|  3 | store3 | NULL | NULL       |

|  5 | store5 | NULL | NULL       |

+----+--------+------+------------+

serine:~/bin% 

 

P.