SQL check if multiple values in group-CodePudding

I have a product table (1) and a fruit list table (2). I group by product ID and check if any fruit from the fruit table (2) is in the group by from table 1 with this group by statement. How can I check if the group by from table 1 also includes 'potato'. So results should be PID 1 and 3

table1

PID | PRODUCT 
1  | potato 
1  | apple 
1  |  banana
1  | melon
1  | peach 
2  | lime 
2  | apple 
2  | pear 
2  | melon 
3  | pear 
3  | apple 
3  | potato 
3  | mango 
3  | plum

table2

FID | FRUIT
1 | peach 
2 | lime 
3 | apple 
4 | pear 
5 | apple 
6 | banana
7 | melon


SELECT PID, COUNT(*)
FROM table1
GROUP BY PID, PRODUCT 
HAVING  PRODUCT in (SELECT FRUIT FROM table2)

CodePudding user response：

Here is one aggregation option. We can left join the first table to the second, and then assert that both a potato record occur as well as at least one fruit from the second table being present.

SELECT t1.PID
FROM table1 t1
LEFT JOIN table2 t2
    ON t2.FRUIT = t1.PRODUCT
GROUP BY t1.PID
HAVING COUNT(CASE WHEN t1.PRODUCT = 'potato' THEN 1 END) > 0 AND
       COUNT(t2.FRUIT) > 0;

CodePudding user response：

try this :

SELECT PID
  FROM table1
 INNER JOIN table2
    ON PRODUCT = FRUIT OR PRODUCT = 'potato'
 GROUP BY PID
HAVING count(DISTINCT PRODUCT) >= 2 AND array_agg(PRODUCT) @> array['potato']