SELECT COUNT INNER JOIN show multiple columns question

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 743 Views - Last Post: 05 March 2018 - 01:32 PM

#16 fearfulsc2   User is offline

  • D.I.C Regular

Reputation: 15
  • View blog
  • Posts: 250
  • Joined: 25-May 16

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 05 March 2018 - 11:38 AM

View Postbaavgai, on 05 March 2018 - 11:27 AM, said:

Sounds like this, then:
select a.ParentId, 
    (case sum(a.Total) when 3 then 1 else 0 end) as InAll
  from (
    select ParentId, 0 as Total from dbo.ParentTable
    union
    select distinct ParentId, 1 from ChildTable1 where Active = 1
    union
    select distinct ParentId, 1 from ChildTable2 where Active = 1
    union
    select distinct ParentId, 1 from ChildTable3 where Active = 1
  ) a
  group by a.ParentId



I don't quite understand what this query is doing. it's returning a list of the Id's and then an InAll column that is full of 0's.

I only need a single row returned with columns that have either a 0 or 1.

Can you elaborate what this query does?
Was This Post Helpful? 0
  • +
  • -

#17 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7434
  • View blog
  • Posts: 15,411
  • Joined: 16-October 07

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 05 March 2018 - 01:32 PM

Easier to offer an example.

Create some test data:
create table #parent ( ParentId int primary key )
create table #child1 ( Child1Id int identity(1,1) primary key, ParentId int not null )
create table #child2 ( Child2Id int identity(1,1) primary key, ParentId int not null )
insert into #parent values(1)
insert into #parent values(2)
insert into #parent values(3)
insert into #parent values(4)
insert into #child1(ParentId) values(1)
insert into #child1(ParentId) values(1)
insert into #child1(ParentId) values(2)
insert into #child1(ParentId) values(4)
insert into #child2(ParentId) values(2)
insert into #child2(ParentId) values(4)



Now, a simple union, no aggregates. Note, I used union instead of union all on the prior examples: this was incorrect.
select ParentId, 0 as Total from #parent
union all
select distinct ParentId, 1 from #child1
union all
select distinct ParentId, 1 from #child2



Results:
ParentId    Total
----------- -----------
1           0
2           0
3           0
4           0
1           1
2           1
4           1
2           1
4           1




Now some grouping:
select a.ParentId, 
    sum(a.Total) as TotalSum,
    (case sum(a.Total) when 2 then 1 else 0 end) as InAll
  from (
    select ParentId, 0 as Total from #parent
    union all
    select distinct ParentId, 1 from #child1
    union all
    select distinct ParentId, 1 from #child2
    ) a
  group by a.ParentId



Results:
ParentId    TotalSum    InAll
----------- ----------- -----------
1           1           0
2           2           1
3           0           0
4           2           1



So, I have four parents. Parent 1 only appears in one table, so not InAll. Parent 2 and 4 are InAll. Parent 3 is in none, but I still want to see it.

Hope this makes sense.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2