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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
TextReportConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
All Courses

From novice to tech pro — start learning today.