Thread: SQL Query help

    #1
  1. Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    421
    Rep Power
    11

    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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    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
  4. #3
  5. Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    421
    Rep Power
    11
    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....
    Captain Planet.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    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
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    421
    Rep Power
    11
    Ahh ok gotcha..... Thanks.
    Captain Planet.

IMN logo majestic logo threadwatch logo seochat tools logo