CODEWITHSUNDEEP
mysqlsqlsubquery
hamchi
hamchi

Reputation: 161

mysql - retrieving a value using sub-select

SELECT  snr, nsname FROM type
WHERE snr in (SELECT snr FROM diet 
WHERE foodnr in (SELECT foodnr FROM food WHERE foodtype = 'FISH' ));

there are more animals that eat the same kind of food BUT eat different types of food in my database, how do i get the animals that only eat that type of food and nothing else.

bah this is impossible to answer without my entire database i feel like an ass just asking this because its so vague but what i am trying to do is i need to filter out the animals that have more foodtypes besides FISH

Upvotes: 2

Views: 213

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

JOIN the tables instead, like so:

SELECT t.snr, t.nsname 
FROM type t 
INNER JOIN diet d ON t.snr = d.snr
INNER JOIN food f ON t.foodnr = f.foodnr
WHERE t.foodtype = 'FISH'
GROUP BY t.foodtype
HAVING COUNT(DISTINCT t.foodtype) = 1;

Upvotes: 3

lc.
lc.

Reputation: 116538

Note I'm assuming the following about your schema:

  • TYPE: snr, nsname
  • DIET: snr, foodnr
  • FOOD: foodnr, foodtype

You can use a NOT EXISTS:

SELECT t.snr, t.nsname 
FROM type t 
INNER JOIN diet d ON t.snr = d.snr
INNER JOIN food f ON d.foodnr = f.foodnr
WHERE f.foodtype = 'FISH'
AND NOT EXISTS
(
    SELECT 1 
    FROM diet dd
    INNER JOIN food ff ON dd.foodnr = ff.foodnr
    WHERE ff.foodtype <> 'FISH'
    AND dd.snr = t.snr
)

Or you can use an anti-join pattern:

SELECT t.snr, t.nsname 
FROM type t 
INNER JOIN diet d ON t.snr = d.snr
INNER JOIN food f ON d.foodnr = f.foodnr AND f.foodtype = 'FISH'
LEFT OUTER JOIN diet dd ON t.snr = dd.snr AND f.foodnr <> dd.foodnr
WHERE f.foodtype = 'FISH'
AND dd.snr IS NULL

Upvotes: 1

Related Questions