HiI 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 is there any way to replace subselect with something more efficient? thanks
