CODEWITHSUNDEEP
mysqlsql
Ian Atkin
Ian Atkin

Reputation: 6346

Produce a list of people that have more than one item

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;

Upvotes: 0

Views: 1229

Answers (2)

user359040
user359040

Reputation:

If you only want to see person and item id's, you don't need to join to person - just access person_item (with a link to item for item_type). However, if you want each combination on a separate line, you will have to access person_item twice - like so:

select pi.person, pi.item
from person_item pi
join (select p.person
      from person_item p
      join item i on p.item = i.item_id and i.type in (1,2,3)
      group by p.person
      having count(*) > 1) c
  on pi.person = c.person

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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.

Upvotes: 1

Related Questions