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
Solved

listing all users in table with Joins

Posted on 2014-10-28
10
166 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

792 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