CODEWITHSUNDEEP
sqlpostgresqljoinsubquery
Philip Kamenarsky
Philip Kamenarsky

Reputation: 2777

Reference alias in subquery

Let's say there's a person table with the columns name and age.

I'm writing a DSL that generates the following SQL:

select *
  from (select * from person p1 inner join person p2 on p1.name = p2.name) as pj;

Now, I would like to be able to access p1 and p2 in the outer query, like this:

select *
  from (select * from person p1 inner join person p2 on p1.name = p2.name) as pj
  where p1.name = 'xxx'; <-- DOESN'T WORK

Something like pj.p1.name would be ideal. Is there a way to achieve this if I don't know the exact column names of person?

Upvotes: 1

Views: 5558

Answers (3)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125404

Use using then just pj.name or even just name

create table person (id serial, name text);
insert into person (name) values ('John'),('Mary');

select *
from (
    select *
    from
        person p1
        inner join
        person p2 using(name)
) r
where name = 'John'
;
 name | id | id 
------+----+----
 John |  1 |  1

A clause of the form USING ( a, b, ... ) is shorthand for ON left_table.a = right_table.a AND left_table.b = right_table.b .... Also, USING implies that only one of each pair of equivalent columns will be included in the join output, not both.

If only one side of the join is necessary:

select *
from (
    select p1.*
    from
        person p1
        inner join
        person p2 using(name)
) r
where name = 'John'
;
 id | name 
----+------
  1 | John

If both join sides are necessary then use records:

select (p2).id, (p1).name -- or (p2).*, (p1).*
from (
    select p1, p2
    from
        person p1
        inner join
        person p2 using(name)
) r
where (p1).name = 'John'
;
 id | name 
----+------
  1 | John

Upvotes: 2

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Fetch Extra Column give proper alias name to it 

select *
from (select *, 
      p1.name as pjName  --Added Column and use this in where clause 
      from person p1 
      inner join person p2 on p1.name = p2.name) as pj
where pjName = 'xxx';

Upvotes: 2

sstan
sstan

Reputation: 36513

The aliases p1 and p2 are not in scope in the join condition. you can only use the pj alias. What you need to do is to be more explicit in the subquery about which columns you are selecting, rather than simply doing SELECT * ...:

Something like:

select *
  from (select p1.name, <add other required columns here> -- explicitly select one of the name columns here
          from person p1 
         inner join person p2 
            on p1.name = p2.name) as pj
  where pj.name = 'xxx' -- use pj alias here.

Upvotes: 2

Related Questions