Jump to content


Photo

MySQL SUBSELETCS or something equivalent...


  • Please log in to reply
3 replies to this topic

#1 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 09 January 2003 - 02:40 PM

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
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 10 January 2003 - 05:07 AM

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
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 10 January 2003 - 10:34 AM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#4 pallevillesen

pallevillesen
  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 10 January 2003 - 11:06 AM

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.
Palle Villesen, www.birc.dk [br]Bioinformatics Research Center




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users