CODEWITHSUNDEEP
mysql
mass6
mass6

Reputation: 96

Need MYSQL Query to select specific child rows only

FX., Given tables for baskets and fruits, looking for a query that returns the basket_id that contains ONLY the two specific fruit.

EX table: fruits

basket_id | fruit
_________________
1         | apple
1         | orange
1         | pear

2         | apple
2         | orange

3         | apple
3         | pear

Given the above table, I'm looking for a query that returns the basket_id that ONLY contains the fruits "apple" and "orange", which would be row 2 only. Basket id 1 should be excluded because it also contains "pear".

Help with this would be most appreciated.

Upvotes: 0

Views: 123

Answers (5)

rrk
rrk

Reputation: 15846

If it is possible to use the list of items as an ordered list you want to select like 'apple,orange', then you can use following.

SELECT f.basket_id, f.fruit
FROM fruits f
     INNER JOIN (
         SELECT basket_id, GROUP_CONCAT(fruit order by fruit) as a
         FROM fruits
         GROUP BY basket_id
         HAVING a = 'apple,orange'
     ) f1 ON f.basket_id = f1.basket_id

Here is the fiddle.

Upvotes: 0

vadim_hr
vadim_hr

Reputation: 549

SELECT DISTINCT basket_id 
FROM fruits f
WHERE 
   NOT EXISTS (SELECT 1 FROM fruits f1 WHERE f.basket_id=f1.basket_id AND f1.fruit NOT IN ('apple', 'orange'))
AND
   (SELECT count(*) FROM fruits f2 WHERE f.basket_id=f2.basket_id AND f2.fruit='apple')=1
AND 
   (SELECT count(*) FROM fruits f3 WHERE f.basket_id=f3.basket_id AND f3.fruit='orange')=1;

I have tested the query on your example. Demo

Upvotes: 0

LONG
LONG

Reputation: 4620

Please note for the example table, I added one more basket which is basket_id = 4, that has two apples only, and it will be a strong test sample for the query.

WITH ABC  --sample table
AS
(
SELECT 1 as basket_id, 'Apple' as fruit
UNION ALL 
SELECT 1 as basket_id, 'Orange' as fruit
UNION ALL 
SELECT 1 as basket_id, 'Pear' as fruit
UNION ALL 
SELECT 2 as basket_id, 'Apple' as fruit
UNION ALL 
SELECT 2 as basket_id, 'Orange' as fruit
UNION ALL 
SELECT 3 as basket_id, 'Apple' as fruit
UNION ALL 
SELECT 3 as basket_id, 'Pear' as fruit
UNION ALL 
SELECT 4 as basket_id, 'Apple' as fruit
UNION ALL 
SELECT 4 as basket_id, 'Apple' as fruit
)
 --main query:   
SELECT basket_id FROM
(
 SELECT *,CASE WHEN fruit in ('Apple','Orange') THEN 1 ELSE 0 END AS row_check
 FROM ABC
 ) as A
GROUP BY basket_id
HAVING COUNT(DISTINCT row_check) =1 -- make sure there is no other fruit 
AND COUNT(DISTINCT fruit) >1   -- make sure there are at least one apple and one orange

output: basket_id 2

Upvotes: 2

mewm
mewm

Reputation: 1277

select
  f.basket_id,
  (select count(1) from fruits where basket_id = f.basket_id) as fruits_in_basket,
  (select count(1) from fruits where basket_id = f.basket_id and fruit in ("apple", "orange")) as specific_fruits_in_basket
from
  fruits as f
group by f.basket_id
having fruits_in_basket = specific_fruits_in_basket

Here is a fiddle: http://sqlfiddle.com/#!9/f4ae3c/37/0

Upvotes: 1

Naincy
Naincy

Reputation: 2943

SELECT DISTINCT(basket_id) 
FROM basket
where fruit IN ('apple', 'orange');

Upvotes: -1

Related Questions