SQL Query help

Dev Shed Forums Sponsor:

July 26th, 2012, 06:26 AM

Captain Planet

Senior Member

Join Date: Aug 2003

Posts: 418

Time spent in forums: 2 Days 13 h 42 m 8 sec
Reputation Power: 10

SQL Query help

Hi, all. Hope you can assist with another of my confusing SQL statements.

I have a list of questions for a particular package. For package there are 2 (possible more in future) processes, and for each process I need to mark whether or not that quality assurance check has been performed. So my front end looks like this:

PackageID = 53

QA Check.............Process1...............Process2
Check 1...............[checkbox]............[checkbox]
Check2................[checkbox]............[checkbox]

At the back-end, I have 4 tables:

Process - list of processes for each package

ProcessID........Process Name
1....................Process1
2....................Process2

Package - list of packages

PackageID......Package Name
1...................Package1
2...................Package2

QAChecks - list of QA checks

QACheckID......QACheckQuestion
1...................Check1
2...................Check2

PackageQAChecks - the results of the QA check per package/process

PackageQACheckID.......PackageID.....ProcessID......QACheckID......Checked
1.......1.......1........1........1
2.......1.......2........1........0

My SQL statement is this:

Code:

SELECT [qaChecks].qaCheckID,[qaChecks].qaCheckQuestion
      ,(CASE WHEN [packageQAChecks].[ProcessID] = 1 Then [checked] 
      WHEN [packageQAChecks].[ProcessID] IS NULL Then 0      
      END) As Process1,
      (CASE WHEN [packageQAChecks].[ProcessID] = 2 Then [checked] 
      WHEN [packageQAChecks].[ProcessID] IS NULL Then 0 END) As Process2
  FROM [ApplicationTracking].[dbo].[qaChecks]
  LEFT JOIN [ApplicationTracking].[dbo].[packageQAChecks]
  ON [packageQAChecks].[qaCheckID] = [qaChecks].[qaCheckID]

The reason I use the LEFT JOIN is because if the checks havent been filled out yet, I still need the questions to display with checkboxes.

Anyway, the results I get is this:

qaCheckID.....qaCheckQuestion....Process1.....Process2
1..................Check1..................1................NULL
1..................Check1...................NULL.........0

But I want:

qaCheckID.....qaCheckQuestion....Process1.....Process2
1..................Check1..................1................0

How can I change my SQL statement to get this result?

Thanks....

__________________
Captain Planet.

July 26th, 2012, 08:10 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,370

Time spent in forums: 3 Months 1 Week 2 Days 6 h 21 m 40 sec
Reputation Power: 4140

Code:

SELECT qaChecks.qaCheckID
     , qaChecks.qaCheckQuestion
     , MAX( CASE WHEN packageQAChecks.ProcessID = 1 THEN checked 
                 WHEN packageQAChecks.ProcessID IS NULL THEN 0      
             END ) As Process1
     , MAX( CASE WHEN packageQAChecks.ProcessID = 2 THEN checked 
                 WHEN packageQAChecks.ProcessID IS NULL THEN 0 
             END ) As Process2
  FROM ApplicationTracking.dbo.qaChecks
LEFT 
  JOIN ApplicationTracking.dbo.packageQAChecks
    ON packageQAChecks.qaCheckID = qaChecks.qaCheckID
GROUP
    BY qaChecks.qaCheckID
     , qaChecks.qaCheckQuestion

i'm not sure if this will work because of the two lines that say THEN 0 -- i think you should let those go NULL instead, and then use COALESCE on the MAX

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

July 26th, 2012, 08:32 AM

Captain Planet

Senior Member

Join Date: Aug 2003

Posts: 418

Time spent in forums: 2 Days 13 h 42 m 8 sec
Reputation Power: 10

Yeah, it appears to work a treat. Thanks....

I tried 'GROUP BY' but obvisouly got the 'is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause'....

Am i correct in saying that because you used MAX (an aggregate) it then allows you to use GROUP BY on the non-aggregated columns?

I guess it confused my that we're using 'MAX' when we only ever return one value for that column.....is this a kind of cheat workaround? Fine by me, but just wondered....

July 26th, 2012, 09:01 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,370

Time spent in forums: 3 Months 1 Week 2 Days 6 h 21 m 40 sec
Reputation Power: 4140

Quote:

Originally Posted by Captain Planet

Am i correct in saying that because you used MAX (an aggregate) it then allows you to use GROUP BY on the non-aggregated columns?

exactly

and it's not a cheat

you had multiple rows...

qaCheckID.....qaCheckQuestion....Process1.....Process2
1..................Check1..................1................NULL
1..................Check1...................NULL.........0

and GROUP BY collapsed them to one ...

qaCheckID.....qaCheckQuestion....Process1.....Process2
1..................Check1..................1................0

July 26th, 2012, 09:38 AM

Captain Planet

Senior Member

Join Date: Aug 2003

Posts: 418

Time spent in forums: 2 Days 13 h 42 m 8 sec
Reputation Power: 10

Ahh ok gotcha..... Thanks.

Developer Shed Advertisers and Affiliates