CODEWITHSUNDEEP
mysqlsqldatabase
Vinicius Santana
Vinicius Santana

Reputation: 4106

How do I make a reference to a table column from the parent query within the subquery in SQL?

Let's say a have a table called people with the following columns:

  1. person_id
  2. name
  3. parent_person_id

I'm trying to write a query to return the following:

  1. person.person_id as 'id'
  2. person.name as 'name'
  3. person.parent_person_id as 'parent id'
  4. person.label (person.person_id = person.person_parent_id from parent query) as 'parent name'.

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

Answers (3)

BeRightBack
BeRightBack

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

Gordon Linoff
Gordon Linoff

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:

  • This would more normally be expressed as a LEFT JOIN, but the correlated subquery is also a very reasonable approach.
  • Notice that the query is easier to follow with table aliases -- and you need them in this case.
  • Don't get in the habit of using single quotes for column names. This often causes confusion and can result in hard to find bugs.
  • Your version doesn't work because in the condition 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

Juan Carlos Oropeza
Juan Carlos Oropeza

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

Related Questions