| Subject | avoid query subselect |
|---|---|
| Author | Mr. John |
| Post date | 2019-07-19T10:48:07Z |
Hi
I have a performance issue on a query where I need all parent record and from child table I only want a flag if there is any child record matching a condition,so I write something like that:
SELECT P.ID,IIF(SUB.ID IS NULL,0,1) AS ISDATA
FROM PARENT P
LEFT JOIN
(SELECT FIRST 1 PA.ID FROM PARENT PA INNER JOIN CHILD C
ON C.ID_PARENT=PA.ID WHERE C.MYFIELD=3) SUB
ON SUB.ID=A.ID
...........
INNER JOIN TABLE3 T3 ON P.ID=T3.....
INNER JOIN TABLE3 T4 ON P.ID=T4.....
..........
I can't use direct left join because for a parent could be more than one record
thanks