2

I'm a self-learning and newbie in SQLite. I have three tables (person, pet, person_pet) and the .schema is:

CREATE TABLE person (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER,
dead INTEGER,
phone_number INTEGER,
salary FLOAT,
dob DATETIME
);

CREATE TABLE person_pet (
    person_id INTEGER,
    pet_id INTEGER,
    );

CREATE TABLE pet (
    id INTEGER PRIMARY KEY,
    name TEXT,
    breed TEXT,
    age INTEGER,
    dob DATETIME,
    purchased_on DATETIME,
    parent INTEGER /*Contains the ID of the pet's parent*/
    );

My task is writing a query that can find all the names of pets and their owners bought after 2004. Key to this is to map the person_pet based on the purchased_on column to the pet and parent. I tried doing a subquery that returns two values but it doesn't work. (Some of you would say: "Obviously".) My question is: if I can't return two values from a subquery how can I achieve this task?

I tried this subquery:

SELECT first_name, name FROM person, pet WHERE person.id pet.id IN(
SELECT person_id FROM person_pet WHERE pet_id IN (
SELECT id FROM pet WHERE purchased_on IN (
SELECT pet.purchased_on
FROM pet, person_pet, person
WHERE person.id = person_pet.person_id AND
pet.id = person_pet.pet_id AND
pet.purchased_on > '2004/01/01 0:0:0 AM'
)
)
SELECT pet_id FROM person_pet WHERE id IN (
SELECT id FROM pet WHERE purchased_on IN (
SELECT pet.purchased_on
FROM pet, person_pet, person
WHERE person.id = person_pet.person_id AND
pet.id = person_pet.pet_id AND
pet.purchased_on > '2004/01/01 0:0:0 AM'
)
)
);

PS: Sorry if my question is a bit long.

Taryn
  • 242,637
  • 56
  • 362
  • 405
Federico
  • 521
  • 5
  • 13

3 Answers3

1

You don't need a sub-query. You need joins:

SELECT p.id, p.first_name, p.last_name, p2.id as pet_id, p2.name as pet_name
FROM person as p
INNER JOIN person_pet as pp on pp.person_id = p.id
INNER JOIN pet as p2 on p2.id = pp.pet_id
WHERE 
p2.purchased_on > '2004/01/01 0:0:0 AM'
Klaus Byskov Pedersen
  • 117,245
  • 29
  • 183
  • 222
  • 1
    Thanks for the tip. However, I don't know what are joins.. Have I to copy-paste this code in an .sql script and then execute it or have I to modify something? Because it prompt out "Error: Incomplete SQL" EDIT: I forgot the semicolon ^^" – Federico Jun 24 '13 at 12:11
0
SELECT a.id, a.first_name, a.last_name, c.id pet_id, c.name pet_name
FROM person a, person_pet b, pet c 
WHERE b.person_id =a.id
and  c.id = b.pet_id
and c.purchased_on > '2004/01/01'

see DEMO HERE

chetan
  • 2,876
  • 1
  • 14
  • 15
0

If you really want to do this with subqueries, you can do it like this:

SELECT (SELECT first_name
        FROM person
        WHERE id = person_pet.person_id),
       (SELECT name
        FROM pet
        WHERE id = person_pet.pet_id)
FROM person_pet
WHERE pet_id IN (SELECT id
                 FROM pet
                 WHERE purchased_on >= '2004');

But joins would be likely to be more efficient.

CL.
  • 173,858
  • 17
  • 217
  • 259