Select from multiple tables where 1 record has more than 1 match in table 2-CodePudding

I have 2 tables, for example one with a person ID, name and food ID for an order and the second with the food ID and food name. I want to join these and return the ID, name, Food ID and Food Name but only for instances where the count of IDs and Food names are > 1 like below. Unfortunately when I try to do this I either get NULL instances from ID or it pulls the Food IDs I'm trying to exclude

Person

ID	Name	Food_ID
1	Joe	3
2	Jill	2
3	Jack	1
1	Joe	1
2	Jill	3
3	Jack	3
1	Joe	4
2	Jill	4
3	Jack	4

Food

Food ID	Food
1	Meat - Fish
2	Veg - Potato
3	Meat - Chicken
4	Veg - Broccoli

ID	Name	Food_ID	Food
1	Joe	3	Meat - Chicken
1	Joe	1	Meat - Fish
3	Jill	1	Meat - Fish
3	Jill	3	Meat - Chicken

I can do it using a temp table to get count of IDs where food like '%Meat%' and count (p.ID) > 1 but I need it to run in just a select query and I've no ID how to approach it as including a where exists just returns me NULL IDs. Apologies for how bad my SQL is but I haven't used it in years and am used to doing all my aggregation in Excel so have little idea how I'm meant to approach it, it's probably a really simple solution

SELECT p.ID, p.Name, f.Food_ID, f.Name
FROM Person p
LEFT JOIN Food f ON p.Food_ID = f.Food_ID
WHERE EXISTS (
    SELECT COUNT(p.ID), COUNT(f.Food_ID)
    FROM Person p
    LEFT JOIN Food f ON p.Food_ID = f.Food_ID
    WHERE f.Food LIKE '%Meat%'
    GROUP BY p.ID
    HAVING COUNT(p.id) > 1 
)
GROUP BY p.ID

CodePudding user response：

Try this:

SELECT
    *
FROM
    Person
    JOIN Food ON Food.Food_ID = Person.Food_ID
WHERE
    Person.ID IN (
        SELECT
            Person.ID
        FROM
            Person
            JOIN Food ON Food.Food_ID = Person.Food_ID
        WHERE
            Food.Food LIKE '%meat%'
        GROUP BY
            Person.ID
        HAVING
            COUNT(*) > 1
        )
ORDER BY
    Person.ID
;

CodePudding user response：

You can use a CTE to get GROUP BY p.ID HAVING COUNT(*) > 1, then get the other column values you need in your main query with a JOIN to your CTE using the p.ID column.

WITH cte AS (
  SELECT 
    p.ID
    FROM Person p
  LEFT JOIN Food f ON p.Food_ID = f.Food_ID
  WHERE f.Food LIKE '%Meat%'
  GROUP BY p.ID
  HAVING COUNT(*) > 1)
SELECT 
  p.ID, 
  p.Name, 
  f.Food_ID, 
  f.Food
FROM Person p
LEFT JOIN Food f ON p.Food_ID = f.Food_ID
INNER JOIN cte ON p.ID = cte.ID
WHERE f.Food LIKE '%Meat%'
ORDER BY p.ID ASC

Fiddle here.

Result:

ID	Name	Food_ID	Food
1	Joe	3	Meat - Chicken
1	Joe	1	Meat - Fish
3	Jack	1	Meat - Fish
3	Jack	3	Meat - Chicken

Note: Based on your provided data, I believe Jack should be listed in your result set, not Jill.