locked
If A Record Exists... RRS feed

  • Question

  • I have one table that contains columns, ID, Chemical, Detected.  I need to write a query that will return a list consisting of ID and Chemical only if it has never been detected.  So in pseudocodeishness:

    SELECT ID, Chemical
    FROM tblChemicals
    WHERE Detected has never been = 1


    Thursday, February 9, 2012 7:36 PM

Answers

  • This is a typical relational division problem. The simplest solution will be

    select ID, Chemical from tblChemicals C where not exists (select 1 from tblChemicals C1 where C1.ID = C.ID and C1.Detected = 1)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Jay Mazz Thursday, February 9, 2012 7:55 PM
    Thursday, February 9, 2012 7:43 PM

All replies

  • Did you try the query you posted with the column detected?

    The code would be

    SELECT ID, Chemical
    FROM dbo.tblChemicals
    WHERE detected = 0


    http://jahaines.blogspot.com/

    Thursday, February 9, 2012 7:38 PM
  • If it is a bit data type use WHERE Detected = 'False'

    Peter Carter http://sqlserverdownanddirty.blogspot.com/

    Thursday, February 9, 2012 7:42 PM
  • This is a typical relational division problem. The simplest solution will be

    select ID, Chemical from tblChemicals C where not exists (select 1 from tblChemicals C1 where C1.ID = C.ID and C1.Detected = 1)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Jay Mazz Thursday, February 9, 2012 7:55 PM
    Thursday, February 9, 2012 7:43 PM
  • Try this

    SELECT ID, Chemical
    FROM tblChemicals d
    WHERE d.ID not in (Select n.Id from n.tblchemicals where n.detected =0) and d.detected = 1
    
    
    


    • Edited by sql393 Thursday, February 9, 2012 7:46 PM
    • Proposed as answer by sql393 Thursday, February 9, 2012 8:17 PM
    Thursday, February 9, 2012 7:43 PM