Solved

listing all users in table with Joins

Posted on 2014-10-28
10
161 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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now