Not sure if I have described the context clearly in the title but the situation is given two tables
A|B
1|1
1|2
2|3
2|4
3|5
4|6
5|7
5|8
B|C
1|NULL
2|1
3|NULL
4|NULL
5|NULL
6|2
7|3
8|4
the condition of output is the value of A should appear more than 1 time, while the corresponding B values are not both NULL in C (at least 1 value of C from B is not NULL)
what matches the conditions of the above table by A should be 1 and 5 the expected output of the count of duplicated A is 2
The following should help.
select t.a,count(t.a),count(t2.c)
from t
join t2
on t.b=t2.b
group by t.a
having count(t.a)>1
and count(t2.c)>=1
Here i join the table t and t2 on the column b after that i check how many records have col-a in table t are >1 and also how many in col c in t2 are> 1.
Just FYI,count(null) would be zero) so any non-null value in t2.c will have count(t2.c)>=1
Full demo
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=6d56b0ed8bafe09786342a6bfb58b8d2
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments