CODEWITHSUNDEEP
sql
Sunder
Sunder

Reputation: 1513

SQL query to indicate presence of a corresponding row

Assuming tables A and B, how do I write a query that would return all columns of A and a flag indicating the presence of a corresponding child row in B

For example:

col1_a|col2_a|...|hasrowinb

Upvotes: 0

Views: 193

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Perhaps the following is the "simplest" way of doing the query, while guaranteeing that there are no duplicates:

select a.*,
       (case when 0 = (select count(*) from b where b.aid = a.aid)
             then 'N'
             else 'Y'
        end) as hasRowInB
from a

Upvotes: 1

jTC
jTC

Reputation: 1350

This will makes sure duplicates aren't created for Table A for each matching record of TableB.

SELECT a.*, b.HasRowInB
FROM TableA a 
LEFT OUTER JOIN (
    SELECT a.id, CASE COUNT(b.id) WHEN 0 THEN 0 ELSE 1 END as HasRowInB
    FROM TableA a
    LEFT JOIN TableB b on a.id = b.a_id
    GROUP BY a.id
) b on a.id = b.id

Upvotes: 1

Hogan
Hogan

Reputation: 70523

Given that b is a child row if b.a_id = a.id then this will work.

Adjust the join to your definition of child.

SELECT a.*, CASE WHEN b.id IS NULL THEN 0 ELSE 1 END AS hasrowinb
FROM a
LEFT JOIN b ON a.id = b.a_id

Upvotes: 2

Related Questions