Solved

listing all users in table with Joins

Posted on 2014-10-28
10
168 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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!

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

732 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