Jump to content

MySQL SUBSELETCS or something equivalent...


pallevillesen

Recommended Posts

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

Link to comment
Share on other sites

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 :D

Link to comment
Share on other sites

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 :D

 

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.