CODEWITHSUNDEEP
sqlpostgresql
Daler
Daler

Reputation: 1205

Subquery in FROM cannot refer to other relations of same query level

I have 4 tables from which I want to select data based on some conditions: organisationunit,orgunitgroupmembers,orgunitgroup, orgunitgroupsetmember

and the select code:

SELECT *
FROM   organisationunit,
       orgunitgroupmembers,
       orgunitgroup,
       orgunitgroupsetmembers
WHERE  organisationunit.comment = '1'
       AND orgunitgroupmembers.organisationunitid = organisationunit.organisationunitid
       AND orgunitgroup.orgunitgroupid = orgunitgroupmembers.orgunitgroupid
       AND orgunitgroupsetmembers.orgunitgroupid = orgunitgroup.orgunitgroupid
       AND orgunitgroupsetmembers.orgunitgroupsetid = '15633'

It does return what I want but now, I want to add these part 

(select name 
 from organisationunit  tt 
 where tt.organisationunitid =organisationunit.parentid) as rayon

after the select method:

SELECT *
FROM   organisationunit,
       orgunitgroupmembers,
       orgunitgroup,
       orgunitgroupsetmembers,
       (SELECT NAME
        FROM   organisationunit tt
        WHERE  tt.organisationunitid = organisationunit.parentid) AS rayon
WHERE  organisationunit.comment = '1'
       AND orgunitgroupmembers.organisationunitid = organisationunit.organisationunitid
       AND orgunitgroup.orgunitgroupid = orgunitgroupmembers.orgunitgroupid
       AND orgunitgroupsetmembers.orgunitgroupid = orgunitgroup.orgunitgroupid
       AND orgunitgroupsetmembers.orgunitgroupsetid = '15633'

and it gives the error

Subquery in FROM cannot refer to other relations of same query level

As you can see the table organisationunit has a parentid, which is a number and the same table organisationunit contains the name for that parentid. So I want at the end of the row in one column show the name of that parentid.

P.S I hope you got want I wanted, otherwise please feel free to ask the questions. Its first time that I do DB manipulations so far, if something is too obvious, believe me its NOT for me.

Upvotes: 0

Views: 2538

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93704

If am not wrong this is what you need. Here I made few changes.

Converted old style of join to proper INNER JOIN and moved the filters to where clause .

Next as mentioned in error you cannot refer the other tables in from clause of subquery. Actually you need to use correlated subquery to find the parent name or self join. or even a cross apply if it is supported

SELECT *,
       (SELECT NAME
        FROM   organisationunit tt
        WHERE  tt.organisationunitid = organisationunit.parentid) AS rayon
FROM   organisationunit
       INNER JOIN orgunitgroupmembers
               ON orgunitgroupmembers.organisationunitid = organisationunit.organisationunitid
       INNER JOIN orgunitgroup
               ON orgunitgroup.orgunitgroupid = orgunitgroupmembers.orgunitgroupid
       INNER JOIN orgunitgroupsetmembers
               ON orgunitgroupsetmembers.orgunitgroupid = orgunitgroup.orgunitgroupid
WHERE  organisationunit.comment = '1'
       AND orgunitgroupsetmembers.orgunitgroupsetid = '15633'

or even a self join to the same table to find the parent details.

SELECT *,
       tt.NAME AS rayon
FROM   organisationunit
       INNER JOIN orgunitgroupmembers
               ON orgunitgroupmembers.organisationunitid = organisationunit.organisationunitid
       INNER JOIN orgunitgroup
               ON orgunitgroup.orgunitgroupid = orgunitgroupmembers.orgunitgroupid
       INNER JOIN orgunitgroupsetmembers
               ON orgunitgroupsetmembers.orgunitgroupid = orgunitgroup.orgunitgroupid
       INNER JOIN organisationunit tt
               ON tt.organisationunitid = organisationunit.parentid
WHERE  organisationunit.comment = '1'
       AND orgunitgroupsetmembers.orgunitgroupsetid = '15633'

Upvotes: 1

Whirl Mind
Whirl Mind

Reputation: 884

To get the name of the parent, use a self-join. Include one more reference to organisationunit in the from clause, with an alias tt or whatever. Something like this : 

select A.*, B.*, C.*, D.*. tt.name as Rayon from organisationunit A,orgunitgroupmembers B,orgunitgroup C, orgunitgroupsetmembers D, organisationunit tt
where organisationunit.comment='1' 
and orgunitgroupmembers.organisationunitid=organisationunit.organisationunitid
and orgunitgroup.orgunitgroupid =orgunitgroupmembers.orgunitgroupid 
and orgunitgroupsetmembers.orgunitgroupid=orgunitgroup.orgunitgroupid
and orgunitgroupsetmembers.orgunitgroupsetid='15633'
and  tt.organisationunitid =A.organisationunit.parentid

Of course, you may want to note that if the datasets are large, self-join may have some performance issues, in that case, you can use multi-step operation after selecting into temporary tables or whatever.

Upvotes: 0

Related Questions