Need help in TSQL Query

Transact-SQL
1

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,

2

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
3

Thank you bitsmed. It worked!

4

Can this query be written in MS Access?

5

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