×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL SELECT COUNT FILTER

SQL SELECT COUNT FILTER

SQL SELECT COUNT FILTER

(OP)
I am currently using the following SQL Statement, which give me Projects and a count of the Child Forms associated to those Projects. However, I only want to return Projects where the Child Form Count is greater than zero (i.e. ChildCount > 0, which obviously doesn't work that way).

SELECT
  Project.ProjectID as ID,
  Project.ProjectName as Name,
  (SELECT COUNT(DISTINCT Form.FormID) FROM Form WHERE   
  Form.ProjectID=Project.ProjectID) as ChildCount
FROM Project  
ORDER BY Name

I have tried putting the (SELECT COUNT(DISTINCT Form.FormID) FROM Form WHERE  Form.ProjectID=Project.ProjectID)>0 in the WHERE clause, but that increased my query time from <1 sec to over 9 sec.

Is there another way to achieve what I am trying to do and still have my SQL perform well?

Thanks in advance for your assistance.

RE: SQL SELECT COUNT FILTER

did you try a join rather than a subquery?

Joins are usually faster than subqueries anyway.

here's a rough try at it

CODE

SELECT
  Project.ProjectID as ID,
  Project.ProjectName as Name,
  F.childcount
FROM Project  
join
select count(*) as ChildCount, formid from Form group by formid having count(*)>0) F
on  F.ProjectID=Project.ProjectID
ORDER BY Name

"NOTHING is more important in a database than integrity." ESquared

RE: SQL SELECT COUNT FILTER

Try this:

CODE

Select Project.ProjectId As Id,
       Project.ProjectName As Name,
       DerivedTableAlias.FormCount
From   Project
       Inner Join (
         Select Form.ProjectId,
                Count(*) As FormCount
         From   Form
         Group By ProjectId
         ) As DerivedTableAlias
         On Project.ProjectId = DerivedTableAlias.ProjectId

If this works for you, and performs well, let me know and I will explain how it works.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: SQL SELECT COUNT FILTER

(OP)
George,
Thanks much, that worked great. Now I need to tie in some security to restrict the Projects and Forms and was doing it with a WHERE CLAUSE, but when I add it in my SQL performance again goes from <1 second to 10s.

WHERE Project.ClientId IN (SELECT DISTINCT ClientId FROM STORE INNER JOIN FLAT_HIER_USER_STORE ON FLAT_HIER_USER_STORE.SVC_LOC_ID = STORE.SvcLocId WHERE FLAT_HIER_USER_STORE.USER_COMMON_NAME = 'qatestra')


If I just use the following SQL with security in the WHERE clause, it is less than a second:

SELECT Project.ProjectID as ID, Project.ProjectName as Name
FROM Project WHERE Project.ClientId IN (SELECT DISTINCT ClientId FROM STORE INNER JOIN FLAT_HIER_USER_STORE ON FLAT_HIER_USER_STORE.SVC_LOC_ID = STORE.SvcLocId WHERE FLAT_HIER_USER_STORE.USER_COMMON_NAME = 'qatestra')
ORDER BY Name

Thanks again for all the help.
Ian

RE: SQL SELECT COUNT FILTER

Instead of using a sub-query, try writing this as a derived table.  I urge you to try it.  If you get stuck, show us what you've got and we'll show you the proper way to do it.  I strongly believe that knowing how to use derived tables will benefit you more than you know.

More specifically, it's your use of the IN clause that is causing your performance problems.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close