|
Binayoke
Starting Member
Australia
1 Posts |
 Posted - 12/10/2012 : 19:22:44
|
Hi,
I have been working my way through the exercises at 'Learn SQL The Hard Way' and im currently stuck on this particular question http://sql.learncodethehardway.org/book/learn-sql-the-hard-waych14.html#x19-5600014.
To keep it simple:
I have a table called PET containing 'id' and 'parent' fields, and a table called PERSON (this table is irrelevant to the question im asking).
Then there is a relation called PERSON_PET containing 'person_id' and 'pet_id' fields.
What I need to know is how to find a pet's parent(owner) by matching the PET.id with the PERSON_PET.pet_id and selecting that record's PERSON_PET.person_id.
Here are the tables:
CREATE TABLE PERSON (
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER,
dead TEXT,
phone_number TEXT,
salary FLOAT,
dob DATETIME
);
CREATE TABLE PET (
id INTEGER PRIMARY KEY,
name TEXT,
breed TEXT,
age INTEGER,
dead TEXT,
dob DATETIME,
parent INTEGER
);
CREATE TABLE PERSON_PET (
person_id INTEGER,
pet_id INTEGER,
purchased_on DATETIME
);
To select the person_id I have the following statement:
SELECT PERSON_PET.person_id
FROM PERSON_PET, PET
WHERE PERSON_PET.pet_id = PET.id
However, how do I make an update query to put this person_id into the parent field? Following is the code I think I need to use, however its incomplete.
UPDATE pet SET parent = (SELECT PERSON_PET.person_id
FROM PERSON_PET, PET
WHERE PERSON_PET.pet_id = PET.id)
WHERE ?????
;
Am I on the right track?
Any help would be appreciated.
Regards,
Peter
|
Edited by - Binayoke on 12/10/2012 19:40:29
|
|
Simple SQL question
