Solved

listing all users in table with Joins

Posted on 2014-10-28
10
165 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

810 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