SQLTeam.com | Weblogs | Forums

Need help in TSQL Query

Hi Team,

I have a data set like below:

PID CID Lvl QCode
1 0 1 AAA
1 1 2 BBB
1 2 2 CCC
1 3 2 DDD
2 0 1 EEE
3 0 1 FFF
4 0 1 GGG
4 9 2 HHH
4 10 2 III
4 11 3 JJJ
4 12 3 KKK

and my output should be like below:

i.e., For every PID (ParentID) if CID (ChildID) is 0 and COUNT(Lvl) > 1 we should eliminate the record with Lvl = 1 and keep remaining records with Lvl > 1.

Ex: PID = 1, CID = 0, Lvl = 1 (This PID has child records so it should be eliminated)
Ex: PID = 2, CID = 0, Lvl = 1 (This PID doesn't have child records so it should remain in the result set)

PID CID Lvl Qcode
1 1 2 BBB
1 2 2 CCC
1 3 2 DDD
2 0 1 EEE
3 0 1 FFF
4 9 2 HHH
4 10 2 III
4 11 3 JJJ
4 12 3 KKK

Please help me with the query.

Thanks in advance,

This should get you started:

select *
  from yourtable as a
 where lvl>1
    or exists(select 1
                from yourtable as b
               where b.pid=a.pid
               group by b.pid
               having count(*)=1
             )
;
1 Like

Thank you bitsmed. It worked!

Can this query be written in MS Access?

It's pretty much generic sql, so should work with ms access.
Do you get an error message?