• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

Correct Joins to produce NULL results

I have the following query

SELECT     CASE WHEN contract.contractid IN (5, 12) THEN 'TV AAP' WHEN contract.contractid IN (6, 13) THEN 'TV Cable' WHEN contract.contractid IN (7, 14)
                      THEN 'TV Civils' END AS contract, jobcat.shortcode AS JobCat, COUNT(job.jobid) AS Orders, SUM(job.autval) AS Value, CASE WHEN datediff(dd,
                      complete.engcomplete, vw_job_abd.abd) >= 0 THEN 'Yes' ELSE 'No' END AS Possible
FROM         complete INNER JOIN
                      job ON complete.jobid = job.jobid INNER JOIN
                      vw_job_abd ON job.jobid = vw_job_abd.jobid INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      jobcat ON job.jobcatid = jobcat.jobcatid
WHERE     (job.contractid IN (5, 6, 7, 12, 13, 14)) AND job.electronic = 1 AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.jobcomplete)))
                      >= CONVERT(DATETIME, '2005-04-01 00:00:00', 102)) AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.jobcomplete)))
                      <= CONVERT(DATETIME, '2005-04-05 00:00:00', 102))
GROUP BY CASE WHEN contract.contractid IN (5, 12) THEN 'TV AAP' WHEN contract.contractid IN (6, 13) THEN 'TV Cable' WHEN contract.contractid IN (7, 14)
                      THEN 'TV Civils' END, jobcat.shortcode, CASE WHEN datediff(dd, complete.engcomplete, vw_job_abd.abd) >= 0 THEN 'Yes' ELSE 'No' END
ORDER BY contract, jobcat.shortcode

This gives the result

contract      Jobcat    Orders     Value               Possible
TV AAP         C            2           3378.14              Yes
TV AAP         D            4           7841.28               No
TV AAP         D            8           14919.79             Yes
TV AAP         E            2            1203.28              Yes

I need the query to give me the following result

contract      Jobcat    Orders     Value               Possible
TV AAP         C            2           3378.14              Yes
TVAAP          C            0           0                        No  
TV AAP         D            4           7841.28               No
TV AAP         D            8           14919.79             Yes
TV AAP         E            2            1203.28              Yes
TV AAP         E            0            0                        No

i.e when  there are no values for Orders or values to still bring in that line anway

Can anybody help

Poly
0
polynominal
Asked:
polynominal
  • 9
  • 8
  • 3
1 Solution
 
Renante EnteraCommented:
Hi polynominal!

You only need to change all "INNER JOIN" with "LEFT OUTER JOIN".

Hope it helps you.  Just try it.


Goodluck!
eNTRANCE2002 :-)
0
 
Renante EnteraCommented:
And by the way, you also need to use COALESCE() function for those records having null value to return 0 when getting the SUM.

:: REVISED QUERY ::

SELECT     CASE WHEN contract.contractid IN (5, 12) THEN 'TV AAP' WHEN contract.contractid IN (6, 13) THEN 'TV Cable' WHEN contract.contractid IN (7, 14)
                      THEN 'TV Civils' END AS contract, jobcat.shortcode AS JobCat, COUNT(job.jobid) AS Orders, SUM(COALESCE(job.autval,0)) AS Value, CASE WHEN datediff(dd,
                      complete.engcomplete, vw_job_abd.abd) >= 0 THEN 'Yes' ELSE 'No' END AS Possible
FROM         complete LEFT OUTER JOIN
                      job ON complete.jobid = job.jobid LEFT OUTER JOIN
                      vw_job_abd ON job.jobid = vw_job_abd.jobid LEFT OUTER JOIN
                      contract ON job.contractid = contract.contractid LEFT OUTER JOIN
                      jobcat ON job.jobcatid = jobcat.jobcatid
WHERE     (job.contractid IN (5, 6, 7, 12, 13, 14)) AND job.electronic = 1 AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.jobcomplete)))
                      >= CONVERT(DATETIME, '2005-04-01 00:00:00', 102)) AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.jobcomplete)))
                      <= CONVERT(DATETIME, '2005-04-05 00:00:00', 102))
GROUP BY CASE WHEN contract.contractid IN (5, 12) THEN 'TV AAP' WHEN contract.contractid IN (6, 13) THEN 'TV Cable' WHEN contract.contractid IN (7, 14)
                      THEN 'TV Civils' END, jobcat.shortcode, CASE WHEN datediff(dd, complete.engcomplete, vw_job_abd.abd) >= 0 THEN 'Yes' ELSE 'No' END
ORDER BY contract, jobcat.shortcode

This should resolved your problem.


Regards!
eNTRANCE2002 :-)
0
 
polynominalAuthor Commented:
Tried that but it doesnt work, still get the same results
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Renante EnteraCommented:
Hmmm ... :-?

Try to use : FULL JOIN instead of LEFT OUTER JOIN.
0
 
polynominalAuthor Commented:
Same thing unfortunately
0
 
lluthienCommented:
my guess,

the effect of using outer join instead of using inner join,
is cancelled out by the fact that you are using a WHERE clause on the joint tables.

so move the where clauses INTO the left joins and you should be okay.

cheers
0
 
polynominalAuthor Commented:
IIuthien I have just started using sql, how would i do that, thanks
0
 
lluthienCommented:
okay..
before we try that,
try reversing this:

FROM         complete LEFT OUTER JOIN
                      job ON complete.jobid = job.jobid LEFT OUTER JOIN

to this:

FROM         job LEFT OUTER JOIN
                       complete ON complete.jobid = job.jobid LEFT OUTER JOIN

i'm assuming the complete - table only contains the records that are complete
0
 
polynominalAuthor Commented:
still get the same results
0
 
polynominalAuthor Commented:
yes you are right about complete
0
 
lluthienCommented:
out to lunch now, i'll have to get back to you
0
 
lluthienCommented:
okay,
so, after switching "job" with "complete", like I said in my previous post,
you have to move the where clause.

this part

AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.jobcomplete)))
                      >= CONVERT(DATETIME, '2005-04-01 00:00:00', 102)) AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.jobcomplete)))
                      <= CONVERT(DATETIME, '2005-04-05 00:00:00', 102))

which is from the where part.
is supposed to be partof the left join on complete, so:

...
from job
LEFT OUTER JOIN complete ON complete.jobid = job.jobid
   AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.jobcomplete)))
                      >= CONVERT(DATETIME, '2005-04-01 00:00:00', 102))
   AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.jobcomplete)))
                      <= CONVERT(DATETIME, '2005-04-05 00:00:00', 102))
left outer join vw_job ...

etc .. etc


then the where clause is just this: (job.contractid IN (5, 6, 7, 12, 13, 14)) AND job.electronic = 1

try that.



0
 
lluthienCommented:
btw, the entire converting of datetime seems a bit over the top.
it looks like you're just trying to determine that the

"jobcomplete" - field ( a datetime a presume)
is between 2005-04-01 and 2005-04-05

am i right in that assumption?

0
 
polynominalAuthor Commented:
You are right in that assumption

I havbe tried the query, the problem is the data is incorrect, there are now values for every shortcode but they are values greater than 1 when they should be 0
0
 
lluthienCommented:
right,
i think i see where that comes from.

in the select, replace the
sum (job.autval),

with this:

CASE
WHEN complete.jobid is null then 0
WHEN complete.jobid not is null then SUM(job.autval) AS Value
END,

so you only get the values for completed jobs.

furthermore some changes to the sql:

the first join should be a LEFT join.
all others can be INNER join.

the conversion of dates is not really necessary i think, so
and complete.jobcomplete <= '2005-04-05'
and complete.jobcomplete >= '2005-04-01'

should suffice, instead of
AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.jobcomplete)))
                      >= CONVERT(DATETIME, '2005-04-01 00:00:00', 102))
AND (CONVERT(datetime, FLOOR(CONVERT(float(24), complete.jobcomplete)))
                      <= CONVERT(DATETIME, '2005-04-05 00:00:00', 102))


could you change these and repost the query you have after those changes?
0
 
polynominalAuthor Commented:
SELECT     CASE WHEN contract.contractid IN (5, 12) THEN 'TV AAP' WHEN contract.contractid IN (6, 13) THEN 'TV Cable' WHEN contract.contractid IN (7, 14) THEN 'TV Civils' END AS contract, jobcat.shortcode AS JobCat, COUNT(job.jobid) AS Orders, CASE WHEN complete.jobid is null then 0 WHEN complete.jobid not is null then SUM(job.autval) AS Value
END, CASE WHEN datediff(dd,  complete.engcomplete, vw_job_abd.abd) >= 0 THEN 'Yes' ELSE 'No' END AS Possible
FROM         complete left outer JOIN
                      job ON complete.jobid = job.jobid INNER JOIN
                      vw_job_abd ON job.jobid = vw_job_abd.jobid INNER JOIN
                      contract ON job.contractid = contract.contractid INNER JOIN
                      jobcat ON job.jobcatid = jobcat.jobcatid
WHERE     (job.contractid IN (5, 6, 7, 12, 13, 14)) AND job.electronic = 1 AND (and complete.jobcomplete <= '2005-04-05'
and complete.jobcomplete >= '2005-04-01'
GROUP BY CASE WHEN contract.contractid IN (5, 12) THEN 'TV AAP' WHEN contract.contractid IN (6, 13) THEN 'TV Cable' WHEN contract.contractid IN (7, 14)
                      THEN 'TV Civils' END, jobcat.shortcode, CASE WHEN datediff(dd, complete.engcomplete, vw_job_abd.abd) >= 0 THEN 'Yes' ELSE 'No' END
ORDER BY contract, jobcat.shortcode
0
 
polynominalAuthor Commented:
Hi, I now have this query

SELECT     CASE WHEN contract.contractid IN (5, 12) THEN 'TV AAP' WHEN contract.contractid IN (6, 13) THEN 'TV Cable' WHEN contract.contractid IN (7, 14)
                      THEN 'TV Civils' END AS contract, jobcat.shortcode, COUNT(job.jobid) AS jobs, CASE WHEN datediff(dd, complete.engcomplete, vw_job_abd.abd)
                      >= 0 THEN 'Yes' ELSE 'No' END AS Possible
FROM         complete INNER JOIN
                      job ON complete.jobid = job.jobid INNER JOIN
                      vw_job_abd ON job.jobid = vw_job_abd.jobid RIGHT OUTER JOIN
                      jobcat LEFT OUTER JOIN
                          (SELECT DISTINCT contractid
                            FROM          job) contract ON 1 = 1 ON job.jobcatid = jobcat.jobcatid AND job.contractid = contract.contractid
WHERE     contract.contractid IN (5, 6, 7, 12, 13, 14)
GROUP BY CASE WHEN contract.contractid IN (5, 12) THEN 'TV AAP' WHEN contract.contractid IN (6, 13) THEN 'TV Cable' WHEN contract.contractid IN (7, 14)
                      THEN 'TV Civils' END, jobcat.shortcode, CASE WHEN datediff(dd, complete.engcomplete, vw_job_abd.abd) >= 0 THEN 'Yes' ELSE 'No' END
ORDER BY contract, jobcat.shortcode

This gives me what I need until I add where job.electronic = 1 then this gets rid of the 0 results, Im not sure how I can add thsi without it affecting the results
0
 
lluthienCommented:
some strange constructions in that one..

try this and tell me what it says.
SELECT    
CASE
WHEN contract.contractid IN (5, 12) THEN 'TV AAP'
WHEN contract.contractid IN (6, 13) THEN 'TV Cable'
WHEN contract.contractid IN (7, 14) THEN 'TV Civils'
END AS contract,
jobcat.shortcode AS JobCat,
COUNT(job.jobid) AS Orders,
CASE
WHEN complete.jobid is null then 0 ELSE SUM(job.autval) END AS Value,
CASE
WHEN datediff(dd,  complete.engcomplete, vw_job_abd.abd) >= 0
THEN 'Yes' ELSE 'No' END AS Possible

FROM job
left outer JOIN complete ON (
      complete.jobid = job.jobid AND
      complete.jobcomplete <= '2005-04-05' AND
      complete.jobcomplete >= '2005-04-01')
INNER JOIN vw_job_abd ON job.jobid = vw_job_abd.jobid
INNER JOIN contract ON job.contractid = contract.contractid
INNER JOIN jobcat ON job.jobcatid = jobcat.jobcatid
WHERE job.contractid IN (5, 6, 7, 12, 13, 14)
AND job.electronic = 1
GROUP BY
CASE
WHEN contract.contractid IN (5, 12) THEN 'TV AAP'
WHEN contract.contractid IN (6, 13) THEN 'TV Cable'
WHEN contract.contractid IN (7, 14) THEN 'TV Civils'
END, jobcat.shortcode,
CASE
WHEN datediff(dd, complete.engcomplete, vw_job_abd.abd) >= 0 THEN 'Yes' ELSE 'No'
END
ORDER BY contract, jobcat.shortcode
0
 
polynominalAuthor Commented:
Hi the results have gone way out of what they should be Im getting figures of 4000 odd when they should be 2 or 3
0
 
lluthienCommented:
'oops' :)


replace
CASE
WHEN complete.jobid is null then 0 ELSE SUM(job.autval) END AS Value,

with
SUM(job.autval) AS Value,

and add
" having not complete.jobid is null " 

just before the order by
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 9
  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now