SQL query to return parents row with only one child record.

Question

User221424154 posted

Need to get only one child record if parents have multiple records with multiple tables also

Answer 1

User-1038772411 posted

Hello Manishamani, 

Try with this below example,

Assuming the primary key of children is (horror) id:

select p.* 

from parents p 
  inner join children c on c.parentId = p.id

where c.firstname like 'tom'
  and not exists
      ( select 1 from children c2
        where c2.parentId = p.id
          and c2.id <> c.id
      ) ;

or using GROUP BY:

select p.* 

from parents p 
  inner join 
    ( select parentid
      from children c 
      group by parentid
      having min(id) = max(id)          -- you could use instead:  count(*)=1  here
         and min(firstname) like 'tom'
    ) c
    on c.parentid = p.id ;

Thank you

Answer 2

User221424154 posted
Not working. Need to get data from multiple tables also

Answer 3

User-1038772411 posted

Hello Manishamani 

As per your description and what i understood  , I have implemented this query with parent and multiple child tables.

select top 1 u.Id,lu.cnt,lu.UserId,cu.scnt,cu.cuid from (
select * from [dbo].[parent] where Deleted=0
)u
left outer join (select COUNT(*)cnt,UserId,Deleted from [dbo].[child1] where Deleted=0 group by UserId,Deleted)lu
on(lu.UserId=u.Id)
left outer join (select COUNT(*)scnt,UserId cuid,Deleted from [dbo].[child2] where Deleted=0 group by UserId,Deleted)cu
on(cu.cuid=u.Id)
-- if multiple table then Like This type of define
where cnt=1 and scnt=1
-- in where conditon to check count =1

If it is not scenario than post your question with your schema(tables)  , so i can try to give you accurate solution.

Thank you

Answer 4

User283571144 posted

Hi Manishamani,

Not working. Need to get data from multiple tables also

According to your description, I couldn't understand your requirement clearly.

Do you mean you want to query just one record from another query which  use inner join to select multiple tables?

Could you please tell me what is the architecture of the tables and result you want to get from the tables?

Best Regards,

Brando