locked
SELECT ... WHERE n of m conditions met RRS feed

  • Question

  • Hello

    I am trying to determine if a row "probably" exists in a table.

    Consider a PERSON table with columns FIRST_NAME, SURNAME, DATE_OF_BIRTH and GENDER.

    How would I write a query that returns true when any three of the four values are present in any one row?

    Thank you

    • Changed type Elvis Long Tuesday, September 16, 2014 4:20 AM it's a question
    Monday, September 8, 2014 12:20 AM

Answers

  • select *  from 
    
    (
    select * from personnel WHERE FIRST_NAME=@FIRST_NAME
    union all
    select * from personnel WHERE SURNAME=@SURNAME
    union all
    select * from personnel WHERE DATE_OF_BIRTH=@DATE_OF_BIRTH
    union all
    select * from personnel WHERE GENDER=@GENDER
    ) t
    
    Group by pid,FIRST_NAME, SURNAME, DATE_OF_BIRTH, GENDER
    Having(count(pid)>=3)

    • Marked as answer by Elvis Long Tuesday, September 16, 2014 4:20 AM
    Monday, September 8, 2014 2:35 AM

All replies

  • If you are using SQL 2012 or above, here is the code. Otherwise, you have to replace IIF with CASE statement

    select * from person
    WHERE IIF(len(FIRST_NAME) > 0, 1, 0) + IIF(len(SURNAME) > 0, 1, 0) + IIF(len(DATE_OF_BIRTH) > 0, 1,0) + IIF(LEN(GENDER) > 0, 1, 0) >= 3


    Satish Kartan http://www.sqlfood.com/

    Monday, September 8, 2014 1:04 AM
  • Sorry, I should have made my question a little clearer.

    Can we simplify this ...

    SELECT * FROM PERSON
    WHERE 
    FIRST_NAME = @FIRSTNAME AND SURNAME = @SURNAME AND DATE_OF_BIRTH = @DOB
    OR
    FIRST_NAME = @FIRSTNAME AND SURNAME = @SURNAME AND GENDER = @GENDER
    OR
    FIRST_NAME = @FIRSTNAME AND DATE_OF_BIRTH = @DOB AND GENDER = @GENDER
    OR
    SURNAME = @SURNAME AND DATE_OF_BIRTH = @DOB AND GENDER = @GENDER
    OR
    
    etc etc for every combination



    • Edited by MikeHan Monday, September 8, 2014 2:15 AM
    Monday, September 8, 2014 2:12 AM
  • select *  from 
    
    (
    select * from personnel WHERE FIRST_NAME=@FIRST_NAME
    union all
    select * from personnel WHERE SURNAME=@SURNAME
    union all
    select * from personnel WHERE DATE_OF_BIRTH=@DATE_OF_BIRTH
    union all
    select * from personnel WHERE GENDER=@GENDER
    ) t
    
    Group by pid,FIRST_NAME, SURNAME, DATE_OF_BIRTH, GENDER
    Having(count(pid)>=3)

    • Marked as answer by Elvis Long Tuesday, September 16, 2014 4:20 AM
    Monday, September 8, 2014 2:35 AM
  • Thank you
    Monday, September 8, 2014 2:51 AM