SQL return all rows and number of references to another table-CodePudding

Given these two tables

A                 B
id                id   a_id
----              ---------
1                 1    1
2                 2    1
3                 3    2
                  4    2
                  5    2
                  6    3
                  7    NULL
                  8    NULL

I need to return all IDs from B and for each ID return if there is more than one reference to A. i.e.

result
B.id  more_than_one_reference
-----------------------------
1     true
2     true
3     true
4     true
5     true
6     false
7     false
8     false

For B.id = 1,2 I need to return true because there are two rows in B with a_id = 1.

For B.id = 3,4,5 I need to return true because there are three rows in B with a_id = 2.

For B.id = 6 I need to return false because there is one row in B with a_id = 3.

For B.id = 7,8 I need to return false because a corresponding a_id value is NULL.

I can see that for my purpose, simple COUNT(B.id) > 1 check (for a given a_id) would be enough but I am unable to join this result to a SELECT B.id FROM B query.

CodePudding user response：

here is one way using window functions :

select * , count(a_id) over (partition by a_id) > 1 more_than_one_reference
from tableB

CodePudding user response：

You can use a self-join:

select b1.id, sum(case when b2.a_id is null then 0 else 1 end) > 0
from b b1 left join b b2 on b1.a_id = b2.a_id and b1.id != b2.id group by b1.id;

Or, using a subquery:

select b1.id, (select sum(b2.a_id = b1.a_id) from b b2) > 1 from b b1

CodePudding user response：

use an inline subquery

declare @tmp1 as table(id int)
declare @tmp2 as table(id bigint identity (1,1),a_id int)

insert into @tmp1 values(1),(2),(3)
insert into @tmp2 values(1),(1),(2),(2),(2),(3),(null),(null)


select more_than_one_reference=(select case when count(*)>1 then 'true' else 'false' end from @tmp2 tmp3 where tmp3.a_id=tmp2.a_id)
from @tmp2 tmp2
join @tmp1 tmp1
  on tmp2.a_id=tmp1.id

output

more_than_one_reference
true
true
true
true
true
false