Post reply

Multiple Search Criteria for SQL Query

  • April 16, 2008 at 12:40 pm

    #121610

    HI

    I have one Table in which we are storing records of PersonID , InfoType , InfoValue like down below

    PersonID Infoype InfoValue

    1 Add1 555

    1 City Edison

    1 State NJ

    -----------------------------------

    2 Add1 8888

    2 City Edison

    2 State Nj

    ----------------------------------------

    3 Add1 555

    3 City Princeton

    3 State NJ

    Now I have to find the PersonID who is having ADD1=555, City=Edison and State=NJ that means it should return only PersonID=1.

    How to write SQL query for this table.Also the condition for filtering records can increase/decrease depending on the parameters chosen at the time of search?(dyamic query) If condition will not work for me here coz its dynamic search the list will goin to increase and will not be limited to 3 condition only.

    Thanks for any kind of help

  • April 16, 2008 at 1:07 pm

    #803281

    select personID

    from mytable

    where

    (infotype='add1' and infovalue='555')

    OR (infotype='City' and infovalue='Edison')

    OR (infotype='State' and infovalue='NJ')

    group by PersonID

    having count(*)=3

    This would assume that non of those infotypes could be duplicated for any person.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply