Reputation: 4106
Let's say a have a table called people with the following columns:
I'm trying to write a query to return the following:
I'm having difficult with the item number 4 subquery.
My question is:
How do I make a reference to the person.person_parent_id from the parent query from within the subquery? I feel like on the code bellow if I could get people.parent_person_id value from the external query and use it on the inner one, I would achieve my goal.
SELECT
people.person_id as 'Person ID',
people.name as 'Person Name',
people.parent_person_id as 'Parent ID',
(
SELECT
people.name
FROM
people
WHERE
people.parent_person_id = people.person_id;
) as 'Parent Name'
FROM
people;
I could be wrong, and I'm definitely open minded. Please share your thoughts and help this good soul move on with his quest.
Upvotes: 2
Views: 845
Reputation: 228
Why not use joins?
SELECT
p1.person_id as 'Person ID',
p1.name as 'Person Name',
p1.parent_person_id as 'Parent ID',
p2.name as 'Parent name'
FROM people p1
LEFT JOIN people p2 ON p1.parent_person_id = p2.person_id;
Upvotes: 1
Reputation: 1271241
You are quite close. You just need proper table aliases:
SELECT p.person_id as PersonID,
p.name as PersonName,
p.parent_person_id as ParentID,
(SELECT parent.name
FROM people parent
WHERE p.parent_person_id = parent.person_id;
) as ParentName
FROM people p;
Notes:
LEFT JOIN
, but the correlated subquery is also a very reasonable approach.people.parent_person_id = people.person_id
, people
refers to the inner from
. You need table aliases to distinguish between the inner and outer references to people
.Upvotes: 4
Reputation: 48207
Just create an alias for each table
SELECT
p1.person_id as 'Person ID',
p1.name as 'Person Name',
p1.parent_person_id as 'Parent ID',
(
SELECT
p2.name
FROM
people as p2
WHERE
p2.parent_person_id = p1.person_id;
) as 'Parent Name'
FROM
people as p1;
Upvotes: 1