How to select columns that have repeating values from another table in SQLite

Question

I am following Zed Shaw's learnSQLthehardway and I wanted to figure out how to select the names from a table person, who owned multiple pets (pets information in table pet.)

person_pet Table:    

person_id    pet_id
    0           0
    0           1
    1           1
    1           2
    2           3

person Table:

    id        name
     0         Zed
     1         Orange
     2         Limen


pet Table:

    id        name
     0         Jag
     1         Black
     2         Fluffy
     3         Mister

I have been trying to use the COUNT function but I can't seem to get the right results. I must return Zed and Orange based off this data.

SELECT name FROM person, person_pet WHERE id = 
(SELECT person_id FROM person_pet GROUP BY person_id HAVING COUNT(person_id) > 1);

This is only returning Zed and not my name. How? What could solve the problem then?

Any help would be great, thank you!

I can see many problems with this query, starting with an error "subquery returns more than one row". However, I don't see how it would only return "Zed" and nothing else. — Gordon Linoff
– Gordon Linoff, Commented Mar 20, 2015 at 21:16

krivtom · Accepted Answer · 2015-03-20 21:23:14Z

1

Use SELECT from person table and IN condition from person_pet table:

SELECT name 
FROM person 
WHERE id IN 
(
    SELECT person_id from person_pet
    GROUP BY person_id
    HAVING COUNT(pet_id) > 1
)

edited Mar 20, 2015 at 21:23

answered Mar 20, 2015 at 21:18

krivtom

25k10 gold badges53 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

OrangeLime Over a year ago

With the exception that it's "id" and not person_id (the first time), thank you!!! IN is what I was missing then. So COUNT basically just returns the number of times a column appears in a table, so it doesn't matter if I got person_id or pet_id

krivtom Over a year ago

Updated query to use id instead of person_id

Collectives™ on Stack Overflow

How to select columns that have repeating values from another table in SQLite

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related