CODEWITHSUNDEEP
sqlsql-server
encc
encc

Reputation: 1633

nesting selects and refere to outer table

I have the following sql statement. I am pulling data from a flat tree structure where i want to select a single follower matching abos_daten.erstellt = (select MAX(erstellt)...

The problem is in order to select the correct MAX(erstellt) I need the following condition where t2.parent_id = t1.parent_id. Unfortunatly t1 can't be bound because it referes to the outer select statement. It seems to create a circle.

select * from trees as t1 inner join abos_daten as starter on t1.parent_id = starter.abonr 
right outer join 
  (select * from trees as t3 inner join abos_daten on t3.child_id = abos_daten.abonr 
   where  abos_daten.erstellt = (select MAX(erstellt) from abos_daten inner join trees as t2 on  t2.child_id = abos_daten.abonr
                                 where t2.parent_id = t1.parent_id and abos_daten.status_id <>  147
                                )
   ) as follower on t1.child_id = follower.abonr

Does anybody know how to solve this? Kind regards, jonatan

Upvotes: 0

Views: 38

Answers (1)

Allan
Allan

Reputation: 17429

To start with, t1 doesn't actually refer to the outer select statement; it refers to another entity in the from clause. As it happens, SQL server has a syntax that specifically allows this kind of functionality: cross apply/outer apply. To use this in you situation, you'd want something like this (untested since I can't re-create your tables):

select * 
from trees as t1 
     inner join abos_daten as starter 
       on t1.parent_id = starter.abonr 
     outer apply (select MAX(erstellt) as max_erstellt
                  from abos_daten 
                       inner join trees as t2 
                         on  t2.child_id = abos_daten.abonr
                  where t2.parent_id = t1.parent_id 
                        and abos_daten.status_id <>  14) as t_m
     right outer join (select * 
                       from trees as t3 
                            inner join abos_daten 
                              on t3.child_id = abos_daten.abonr) as follower 
       on t1.child_id = follower.abonr 
          and t_m.max_erstellt = follower.erstellt

Upvotes: 2

Related Questions