MySQL: Produce a list of people who have more than one object

advertisements

Say I have a table of people...

person:
-----------
id | person
---+-------
 1 | Jim
 2 | Bob
 3 | Frank

...and I have a table of items...

item:
----------------
id | item | type
---+------+-----
 1 |   21 |    2
 2 |   10 |    5
 3 |   11 |    1
 4 |    9 |    1

...and I also have a table describing who has what...

person_item:
-------------
item | person
-----+-------
   1 |      2
   2 |      1
   3 |      1

How can I create a single query that will tell me when an individual has more than one item of a particular type? I only want the query to concern itself with items of type (1, 2, 3).

The results from the query should be in the following format:

---------------
person  |  item
        |  item
--------+------
person  |  item
        |  item
        |  item
--------+------
... etc.

This is what I have tried... but it produces garbage...

SELECT person.id, item.id FROM person_item AS pi
JOIN item AS i ON i.id = pi.item
JOIN person AS p ON p.id = pi.item
WHERE item.type IN (1,2,3)
HAVING COUNT(pi.person) > 1;


The query is suspect because you have a having clause but not a group by clause. Also, you are using table names when you have very reasonable aliases. And, you want to count distinct items within a person/type combination, not just for a person.

Taking these into account, try this query:

SELECT p.id, i.type, group_concat(i.item) as items
FROM person_item pi join
     item i
     ON i.id = pi.item join
     person p
     ON p.id = pi.person
WHERE i.type IN (1,2,3)
group by p.id, i.type
HAVING COUNT(distinct i.id) > 1;

This also provides the list of items as the third things returned.