Get TOP rows out of child table WITH inner join

Question

I want to inner join with a child table based on ID and get the top Row of the child table, I am not joining to take any data out of the child table, its just to validate that child table record exists for parent table. If I dont include TOP row there is chances of getting multiple rows of parent in the result set.

-- Chances of multiple rows in resultset for same PARENTID is possible

SELECT P.PARENTID FROM PARENT P
INNER JOIN CHILD C ON C.PARENTID = P.PARENTID and C.ISACTIVE = 1

I need something like

SELECT P.PARENTID FROM PARENT P
INNER JOIN (SELECT TOP 1 * FROM CHILD ) AS C
ON C.PARENTID = P.PARENTID AND C.ISACTIVE = 1

I not sure how to get it working I am curious if somebody can help me out or provide me any url where I could find the solution

Answer 1

Wouldn't it be better to do something like this:

SELECT * FROM parent WHERE parentId not in ( select parentid from child where isactive = 1 )

By doing this, you'll get a list of all the records in 'parent' that have no associated records in 'child'.

In order to retrieve the parents that have records in child, just reverse the criteria:

select * from parent where parentId in ( select distinct parentId from  child where isactive = 1 )

I've specified distinct here, in order to be sure that each parentId is listed only once, so that the parent itself is returned only once in the resultset as well.

Answer 2

Do you think WHERE EXISTS will do the job ?

SELECT P.PARENTID FROM PARENT P 
WHERE EXISTS (SELECT 1  FROM CHILD C 
WHERE C.PARENTID = P.PARENTID 
AND C.ISACTIVE = 1)

Answer 3

Possibly not the most efficient way, but:

SELECT p.parentid FROM parent p
WHERE (SELECT COUNT(*) FROM child c WHERE c.parentid = p.parentid AND c.isactive)
    >= 1