Solved

listing all users in table with Joins

Posted on 2014-10-28
10
167 Views
Last Modified: 2014-11-02
hey guys,

In MSSQL, I have a table with a bunch of auditors. Long story short, i want to show ALL my auditors listed out. The problem is when i use where clause, it stops showing all the auditors and only shows the ones that meet the criteria of the WHERE clause. Is there a way to show all of the auditors in the table regardless of the where clause. So if they don't meet the criteria just have the SUM and COUNT = 0?

Here's my sql ( i cut it short so it's an easier read...):

SELECT
  auditors.auditorID
 ,auditors.fName

, COUNT(DISTINCT case when claims.dateon >='20140928' AND claims.dateon < '20141028' AND entries.errorCode NOT IN('DP','RB','WP','PE') then claims.rID end) as rTotal30
, SUM(case when claims.dateon >= '20140928' AND claims.dateon < '20141028' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate30

FROM auditors
INNER JOIN claims ON claims.auditorID = auditors.auditorID
INNER JOIN entries ON claims.rID = entries.rid

WHERE claims.status = 'closed'
AND (claims.dateon >= '20140630' AND claims.dateon < '20141028')

GROUP BY
  auditors.auditorID
, auditors.fname

ORDER BY auditors.fName

Open in new window

0
Comment
Question by:engineroom
  • 6
  • 4
10 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40409833
Change the where conditions into join conditions and the joins to LEFT JOIN

FROM auditors
      LEFT JOIN claims
                  ON claims.auditorID = auditors.auditorID
                        AND claims.status = 'closed'
                        AND (claims.dateon >= '20140630' AND claims.dateon < '20141028')
      LEFT JOIN entries
                  ON claims.rID = entries.rid

Open in new window


in full
SELECT
      auditors.auditorID
    , auditors.fName

    , COUNT(DISTINCT CASE
            WHEN claims.dateon >= '20140928' AND
            claims.dateon < '20141028' AND
            entries.errorCode NOT IN ('DP', 'RB', 'WP', 'PE') THEN claims.rID
      END) AS rTotal30
    , SUM(CASE
            WHEN claims.dateon >= '20140928' AND
            claims.dateon < '20141028' AND
            entries.errorCode NOT IN ('DP', 'RB', 'WP', 'PE') THEN entries.refundDue
            ELSE 0.0
      END) AS rate30

FROM auditors
      LEFT JOIN claims
                  ON claims.auditorID = auditors.auditorID
                        AND claims.status = 'closed'
                        AND (claims.dateon >= '20140630' AND claims.dateon < '20141028')
      LEFT JOIN entries
                  ON claims.rID = entries.rid

GROUP BY
      auditors.auditorID
    , auditors.fname

ORDER BY
      auditors.fName

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40409848
For explanation, there are 2 reasons why you were not getting all Auditors.

1. INNER JOIN

These will require that a match exists in BOTH tables, so if an Auditor is not referenced in dbo.claims that Auditor wwould not be listed.

2. WHERE clause

Even IF you had used LEFT JOINs; if you refer to conditions through the where clause they must also be obeyed. The conditions used would require that an Auditor was referenced in dbo.claims.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40409880
mmmmm

The dates you have used in the case expressions don't match the dates used in the where clause; they probably should and indeed you might also place the NOT IN condition into the where clause also so the overall query is simplified.
SELECT
      auditors.auditorID
    , auditors.fName
    , COUNT(DISTINCT claims.rID) AS rTotal30
    , SUM(entries.refundDue) AS rate30
FROM auditors
      LEFT JOIN claims
                  ON claims.auditorID = auditors.auditorID
                        AND claims.status = 'closed'
                        AND (claims.dateon >= '20140928' AND claims.dateon < '20141028')
      LEFT JOIN entries
                  ON claims.rID = entries.rid
                        AND  entries.errorCode NOT IN ('DP', 'RB', 'WP', 'PE')
GROUP BY
      auditors.auditorID
    , auditors.fname
ORDER BY
      auditors.fName

Open in new window

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 3

Author Comment

by:engineroom
ID: 40410520
Hey PortletPaul,

The dates don't match cause i deleted a bunch of other selects in there. The statement is very similar to the other one and, as a matter-of-fact, not all the clients are showing in the other question, i might have to do the same thing there. So the answer is get rid of the WHERE and put it in a LEFT JOIN. You would be proud, changing to a LEFT JOIN was the first thing i did, based upon what i learned from before!  :)
0
 
LVL 3

Author Comment

by:engineroom
ID: 40410528
I was able to get it to work, but only when i made BOTH Joins LEFT JOINS. Why is that? Here's my code:

SELECT
auditors.auditorID
, auditors.fName

, COUNT(DISTINCT case when claims.dateon >='20140914' AND claims.dateon < '20141029' AND entries.errorCode NOT IN('DP','RB','WP','PE') then claims.rID end) as rTotal30
, SUM(case when claims.dateon >= '20140914' AND claims.dateon < '20141029' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate30
, COUNT(DISTINCT case when claims.dateon >='20140914' AND claims.dateon < '20141029' AND entries.errorCode IN('DP','RB','WP','PE') then claims.rID end) as pTotal30
, SUM(case when claims.dateon >= '20140914' AND claims.dateon < '20141029' AND entries.errorCode IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as payment30
, COUNT(DISTINCT case when claims.dateon >='20140731' AND claims.dateon < '20140913' AND entries.errorCode NOT IN('DP','RB','WP','PE') then claims.rID end) as rTotal60
, SUM(case when claims.dateon >= '20140731' AND claims.dateon < '20140913' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate60
, COUNT(DISTINCT case when claims.dateon >='20140731' AND claims.dateon < '20140913' AND entries.errorCode IN('DP','RB','WP','PE') then claims.rID end) as pTotal60
, SUM(case when claims.dateon >='20140731' AND claims.dateon < '20140913' AND entries.errorCode IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as payment60
, COUNT(DISTINCT case when claims.dateon >='20140616' AND claims.dateon < '20140730' AND entries.errorCode NOT IN('DP','RB','WP','PE') then claims.rID end) as rTotal90
, SUM(case when claims.dateon >='20140616' AND claims.dateon < '20140730' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate90
, COUNT(DISTINCT case when claims.dateon >='20140616' AND claims.dateon < '20140730' AND entries.errorCode IN('DP','RB','WP','PE') then claims.rID end) as pTotal90
, SUM(case when claims.dateon >='20140616' AND claims.dateon < '20140730' AND entries.errorCode IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as payment90
, COUNT(DISTINCT case when claims.dateon >='20140502' AND claims.dateon < '20140615' AND entries.errorCode NOT IN('DP','RB','WP','PE') then claims.rID end) as rTotal120
, SUM(case when claims.dateon >= '20140502' AND claims.dateon < '20140615' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate120

, COUNT(DISTINCT case when claims.dateon >='20140502' AND claims.dateon < '20140615' AND entries.errorCode IN('DP','RB','WP','PE') then claims.rID end) as pTotal120
, SUM(case when claims.dateon >='20140502' AND claims.dateon < '20140615' AND entries.errorCode IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as payment120
FROM auditors

LEFT JOIN claims ON claims.auditorID = auditors.auditorID AND claims.status = 'closed' AND (claims.dateon >= '20140502' AND claims.dateon < '20141029')
LEFT JOIN entries ON claims.rID = entries.rid

GROUP BY
auditors.auditorID
, auditors.fname

ORDER BY auditors.fName

Open in new window

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40410566
Both joins must be left joins because it is a chain of relationships.
An OUTER join is used "to allow nulls"  (here we are using a LEFT OUTER JOIN)

i.e. to list all auditors we want to allow a "null claims"
so, we also have to allow "null entries" too

and hence BOTH tables require an OUTER JOIN

(& LEFT OUTER JOIN can be abbreviated to just LEFT JOIN)

clear as mud I bet

let's put it this way.
As soon as you create an INNER JOIN, everything has to match and the NULLs we need aren't allowed.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40410571
btw
I am proud of you ... well done
0
 
LVL 3

Author Comment

by:engineroom
ID: 40410690
Thanks! One last question, in the LEFT JOIN.... claims.dateOn>='2014...  is that even necessary considering the SELECT's above take care of the date range?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40411949
The conditions inside the case expressions are evaluated on every row that satisfies the combined FROM and WHERE clauses

(the FROM and WHERE clauses are performed before the select clause)

So every row you REMOVE in the FROM and WHERE clause will reduce the effort expended in that SELECT clause

so, absolutely you want to include the date range filter in the join conditions.

Now please take a look at ID: 40409880 again
you will see there that by filtering out all unnecessary rows that an overall query can be simplified. As it turns out your case expressions are more complex than that sample, but you get the idea I hope.
0
 
LVL 3

Author Closing Comment

by:engineroom
ID: 40418768
You rock! Thanks for all your help. I've learned a lot from you!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Regarding Disk IO 3 42
SQL Server Compression Decision 5 37
SQL syntax question 6 37
Delete old Sharepoint backups 2 11
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question