Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2006
    Posts
    82

    Unanswered: Quick select statement question

    hi all

    another easy one

    how would i write it to where i select a value = 0 where all are equal to 0 rather than one line. when selecting based off a key from another table how would i select the values that all bring back 0 for that key rather than just a line or 2? i hope that makes sense, lol.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    What have you come up with so far?

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    select case when exists(select * from MyTable where MyValue <> 0) then 1 else 0 end
    Regards
    Kris Zywczyk

  4. #4
    Join Date
    Mar 2006
    Posts
    82
    Code:
    SELECT     dbo.tPA00175.chrJobNumber, dbo.tPA00175.intJobKey, dbo.tPA00125.numQuantityToInv
    FROM         dbo.tPA00125 INNER JOIN
                          dbo.tPA00175 ON dbo.tPA00125.intJobKey = dbo.tPA00175.intJobKey
    WHERE     (dbo.tPA00125.numQuantityToInv = 0)

    but this is only selecting the single values...id like to select the ones that have all 0's for the particular jobkey

  5. #5
    Join Date
    Nov 2006
    Location
    UK
    Posts
    46
    How about:

    Code:
    select dbo.tPA00175.chrJobNumber, 
           dbo.tPA00175.intJobKey, 
           numQuantityToInv = 0 
    from dbo.tPA00175 inner join (select intJobKey
                                  from dbo.tPA00125
                                  group by intJobKey
                                  having max(case when numQuantityToInv = 0 then 0 else 1 end) = 0
                                  ) as t2 on dbo.tPA00175.intJobKey = t2.intJobKey
    Regards
    Kris Zywczyk

  6. #6
    Join Date
    Mar 2006
    Posts
    82
    thanks, that works nicely

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •