What is the exact query for below [closed]

I am beginner in SQL This is my patient table

id patientName gender familyType mobileAppId
1 xyz m p 1
2 abc m c 1
3 pqr f c 2
4 ghi f p 3
5 lmn f c 3
6 ppp f c 3
7 kkk f c 1

if i search id = 1 then , i need to get id 1 and 2 row because id 1’s family type is ‘p’ so it return mobile app id is same rows

if i search id = 2 i need to get only id 2nd row(bcz it’s family type = c)

if i search id = 3 i need to get only id 3rd row(bcz it’s family type = c)

if i search id = 4 i need to get 4th,5th,6th row(bcz search id family type = p and mobile app id is same)

note : mobileAppId have only one ‘p’ familyType and may be more than 1 ‘c’ familyType

I tried many conditions but i did’t get expected result

any one please help me?

Answer

The following query work:

select * 
from patient
where 
  (id = <searchId> and familyType = 'c')
  or mobileAppId = (
    select mobileAppId from patient
    where familyType = 'p' and id = <searchId>
  )

Online demos:

Note: for searchId = 1 the row with id = 7 is also returned because mobileAppId = 1

Leave a Reply

Your email address will not be published. Required fields are marked *