SQL Query With Count and IS NULL

Hi,

I have two different tables that I have performed an inner join to but I need the query to count the number of values from both. When Running the query that is show below i get nothing.
And help is much appreciated

SELECT r.SId, a.Closed, Count (*) as Total
FROM Req r INNER JOIN Archive a on (a.requestid = r.requestid )      
WHERE (((r.SId) ='H') AND ((a.Closed) IS NULL))
GROUP BY r.SId, a.Closed

R8VIAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TextReportCommented:
Try changing it from a INNER JOIN to a LEFT OUTER JOIN. You may also want to consider changing the FROM

FROM Req r
    INNER JOIN (SELECT requestid, closed
                        FROM Archive
                        WHERE closed IS NULL)  a
    ON a.requestid = r.requestid      
WHERE r.SId ='H'

Cheers, Andrew
0
R8VIAuthor Commented:
Hi,

I have tried the following but still get no number back

SELECT r.SId, a.Closed, Count (1) as Total
FROM Req r INNER JOIN (Select requestid, closed From Archive where closed IS NULL)
a on a.requestid = r.requestid where r.SId ='HEL'
GROUP BY r.SId, a.Closed;

Thanks Again Advance
0
TextReportCommented:
Are you sure there are records that meet both of your criteria. Now change it to a LEFT OUTER JOIN from an INNER JOIN.
Cheers, Andrew
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

R8VIAuthor Commented:
Sorry should have tried this first but still something wrong i think

SELECT r.SId, a.Closed, Count (*) as Total
FROM Requests r LEFT OUTER JOIN (Select requestid, closed From Archive where closed IS NULL)
a on a.requestid = r.requestid where r.SId ='HEL'
GROUP BY r.Sd, a.Closed;

I get the results:

Sld = HEL
CLOSED = NULL
Total = 611

However it hasnt added the null values thats what i missing

Any help is much appreciated
Thanks,
Again

R8VI
0
TextReportCommented:
but you are joining on requestid between the 2 tables so if the INNER JOIN returns no records then there are no records in the archive table for any of the SID = 'HEL'

What happens when you remove the WHERE condition completely?

Cheers, Andrew
0
R8VIAuthor Commented:
Hi i understanding what u saying about removing the where clause did that and its shows it wth out addin it.
But I have done these two different quries on the two different tables and both retun values

SELECT count (SID) AS FromRequests From Requests Where SID='HEL';
This return 611

Select Count(1) As Helpdesk
From ArchiveReq
Where Closed IS NULL;
This returns 16

Where as the combination only returns 611

Thanks Again
0
TextReportCommented:
Are you sure this is a 1 to Many relationship we are trying to Query and not a UNION.
Is Archive the Archive of Requests?
Cheers, Andrew
0
R8VIAuthor Commented:
This is a database that is already created i am pretty sure it is a 1-2 many.
And sorry this is how the second query is

Select Count(1) As Helpdesk
From Archive
Where Closed IS NULL;
This returns 16

both different tables.
0
TextReportCommented:
OK the code below will only return records where there is a link between the requests table and the archive table.  Please run this as is and then run it as a LEFT OUTER JOIN.

Cheers, Andrew
SELECT r.SId
     , a.Closed
     , Count(r.SId) as Total 
     , Count(a.requestid) AS ArchiveCount 
FROM Req r 
    INNER JOIN (SELECT requestid, closed 
                FROM Archive 
                WHERE closed IS NULL)  a 
    ON a.requestid = r.requestid      
WHERE r.SId ='HEL'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.