• Status: Solved
• Priority: Medium
• Security: Public
• Views: 232

# Rehash of previous Join Question

Thanks to help on here and part myself I have the following query which gives me the  correct results,

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 (7, 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

The results are in the format

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

The problem occurs when I add more clauses to the Where clause

i.e. WHERE     contract.contractid IN (7, 14)

becomes

WHERE     contract.contractid IN (7, 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))

I lose all the results where the count is 0 and the results are

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

How can I get the 0 counts back

Thanks for everybodys help on this so far

Mark

0
polynominal
• 2
1 Solution

Commented:
That's because you have a left outer join on the table you are adding the WHERE statement for.  The zero values occur when there is no matching record (NULL) in that table so restricting the output to particular contractid's eliminates those records.

...LEFT OUTER JOIN (SELECT DISTINCT contractid FROM job) contract
ON 1 = 1 ON job.jobcatid = jobcat.jobcatid
AND job.contractid = contract.contractid
AND contract.contractid IN (7, 14)
...

0

Commented:
otherwise you have to allow for nulls in contractid...

not sure about the syntax but you can try

WHERE ... contract.contractid in (7,14,NULL) ...
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.