SELECT COUNT INNER JOIN show multiple columns question

  • (2 Pages)
  • +
  • 1
  • 2

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

#1 fearfulsc2   User is offline

  • D.I.C Regular

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

SELECT COUNT INNER JOIN show multiple columns question

Posted 01 March 2018 - 01:58 PM

Hi everyone, I am trying to do a select statement that will show an integer value of 0 or 1 depending on if multiple tables have a value with respect to a give id. For example

SELECT COUNT(DISTINCT dbo.ParentTable.ParentId) as SomeName FROM dbo.ParentTable
INNER JOIN dbo.ChildTable1 ON dbo.ParentTable.ParentId = dbo.ChildTable1.ParentId
INNER JOIN dbo.ChildTable2 ON dbo.ParentTable.ParentId = dbo.ChildTable2.ParentId
INNER JOIN ..........
WHERE dbo.ParentTable.ParentId = SOMENUMBER
AND dbo.ChildTable1.Active = 1
AND dbo.ChildTable2.Active = 1
AND .........

UNION ALL

SELECT COUNT(DISTINCT dbo.ParentTable.ParentId) as SomeName2 FROM dbo.ParentTable
INNER JOIN dbo.ChildTable3 ON dbo.ParentTable.ParentId = dbo.ChildTable3.ParentId
.........
WHERE dbo.ParentTable.ParentId = 1
AND dbo.ChildTable3.Active = 1
.......



So what this returns is a list of two rows either with a value of 1 or 0 depending on if the queries above are met.

How do I make this into columns instead?

Is This A Good Question/Topic? 0
  • +

Replies To: SELECT COUNT INNER JOIN show multiple columns question

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • Posts: 60,467
  • Joined: 12-June 08

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 01 March 2018 - 02:13 PM

Are they all on a similar key? Maybe put them in subqueries and join on it.
Was This Post Helpful? 0
  • +
  • -

#3 fearfulsc2   User is offline

  • D.I.C Regular

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

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 01 March 2018 - 02:16 PM

Yes, they are all have the same foreign key reference to the parent table. So Iím trying to figure out how to separate them just like above but have them display as columns instead of as rows
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • Posts: 60,467
  • Joined: 12-June 08

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 01 March 2018 - 02:22 PM

That's what the join on the subqueries do - make it all one row.
Was This Post Helpful? 0
  • +
  • -

#5 fearfulsc2   User is offline

  • D.I.C Regular

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

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 01 March 2018 - 02:24 PM

Hm okay. The example I gave was basic, but I assume I can use the same methodology when itís going to be 6 of these? So I want 6 columns but get 6 rows.

Do I just keep nesting queries?
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • Posts: 60,467
  • Joined: 12-June 08

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 01 March 2018 - 02:31 PM

Most certainly. Similar to joining of six tables at once you can do the same thing with six subqueries.

Heck you can cheat a bit and join on 1=1
cr eate table #foo (lval int, sval varchar(5))

ins ert into #foo(lval, sval)values(1, '1')
ins ert into #foo(lval, sval)values(1, '6')
ins ert into #foo(lval, sval)values(1, '4')
ins ert into #foo(lval, sval)values(2, '1')
ins ert into #foo(lval, sval)values(3, '1')

select *
from
(
select lval, count(*) as mycount1
from #foo
WHERE lval = 1
group by lval
) a
join (
select lval, count(*) as mycount2
from #foo
WHERE lval = 2
group by lval
) b on 1 = 1
join (
select lval, count(*) as mycount3
from #foo
WHERE lval = 3
group by lval
) c on 1 = 1


dr op table #foo 



lval        mycount1    lval        mycount2    lval        mycount3
----------- ----------- ----------- ----------- ----------- -----------
1           3           2           1           3           1

Was This Post Helpful? 1
  • +
  • -

#7 fearfulsc2   User is offline

  • D.I.C Regular

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

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 05 March 2018 - 09:12 AM

Question:

How server intensive could this be in a production environment? The reason why I ask is because some of the sections I'm working with have 10+ tables to Inner Join and I'm doing this for every section. Some sections have 1 table while others may have 10+.

Is this query going to cause a lot of resource overhead or is it okay due to it being a select and select statements by nature aren't really intensive?

And if this is highly inefficient, what would be a better way or the best way to do something similar to this?

Triggers?
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • Posts: 60,467
  • Joined: 12-June 08

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 05 March 2018 - 09:16 AM

I avoid triggers like the plague. Always ends up fudged up or kicks a crap train reaction.

It shouldn't, in theory, be too bad if at all, but you'll have to determine with your own actual tables, columns, etc. Keep an eye on the execution plans too for performance!
Was This Post Helpful? 0
  • +
  • -

#9 fearfulsc2   User is offline

  • D.I.C Regular

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

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 05 March 2018 - 09:20 AM

Hmm okay. The reason why I ask is because this query/stored procedure will run every single time a user goes to a particular page.

Our application will be used by 100+ people and the concern is what if all 100+ people go to the same page at the same time? Each user will have this execute and I am concerned if the overhead will be very high.

So I don't know of an efficient way to do this unless I put a check/query for each time a user updates a table or adds a value or something for each table which will also be intensive in my opinion.
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15113
  • View blog
  • Posts: 60,467
  • Joined: 12-June 08

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 05 March 2018 - 09:25 AM

Again, in theory with the generalized information given, I don't think it will be a massive burden. Just keep all the 'nolocks' in place and eye on indexs and execution plans.
Was This Post Helpful? 0
  • +
  • -

#11 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2878
  • View blog
  • Posts: 11,258
  • Joined: 03-December 12

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 05 March 2018 - 09:31 AM

I have given this same advice earlier in the day. If you are at a point where you see a performance hit, add a replication read-only database. Now, in your case it is slightly different and you need to determine the actual bottlenecks if there are any. 10 tables amounts to a lot of joins. You need to analyze the execution to see if there are improvements that can be made in the query, or the statistics file first, see if indexes need to be added, removed, or updated and the like. Caching may also be something to look into. Do users REALLY need to run the query everytime they hit the page? Or is it something that can be stored?
Was This Post Helpful? 0
  • +
  • -

#12 fearfulsc2   User is offline

  • D.I.C Regular

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

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 05 March 2018 - 09:33 AM

It's most likely something that can be stored as data will not be changed very often/frequently
Was This Post Helpful? 0
  • +
  • -

#13 baavgai   User is online

  • Dreaming Coder
  • member icon


Reputation: 7448
  • View blog
  • Posts: 15,442
  • Joined: 16-October 07

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 05 March 2018 - 10:35 AM

Two thoughts.

If you want to know all the children counts:
SELECT a.ParentId as SomeName, 
    c1.ct, c2.ct, c3.ct
  FROM dbo.ParentTable a
    LEFT JOIN (select ParentId, count(*) as ct from ChildTable1 where Active = 1 group by ParentId) as c1 ON c1.ParentId=a.ParentId
    LEFT JOIN (select ParentId, count(*) as ct from ChildTable2 where Active = 1 group by ParentId) as c2 ON c2.ParentId=a.ParentId
    LEFT JOIN (select ParentId, count(*) as ct from ChildTable3 where Active = 1 group by ParentId) as c3 ON c3.ParentId=a.ParentId
  WHERE a.ParentId = ?



If, instead, you want some grand total:
select ParentId, sum(Total) as Total
  from (
    select ParentId, 0 as Total from dbo.ParentTable
    union
    select ParentId, count(*) from ChildTable1 where Active = 1 group by ParentId
    union
    select ParentId, count(*) from ChildTable2 where Active = 1 group by ParentId
    union
    select ParentId, count(*) from ChildTable3 where Active = 1 group by ParentId
  ) a
  group by ParentId


Was This Post Helpful? 0
  • +
  • -

#14 fearfulsc2   User is offline

  • D.I.C Regular

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

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 05 March 2018 - 10:37 AM

@baavgai,

I do not need to know the total count. All I need to know is if the table has at least one value in it for a given ID. And when all the tables have a value, it will return a 1, otherwise it will return a 0.

Issue I'm facing now is a design question in whether this query will cause a lot of overhead in a production environment
Was This Post Helpful? 0
  • +
  • -

#15 baavgai   User is online

  • Dreaming Coder
  • member icon


Reputation: 7448
  • View blog
  • Posts: 15,442
  • Joined: 16-October 07

Re: SELECT COUNT INNER JOIN show multiple columns question

Posted 05 March 2018 - 11:27 AM

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


Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2