where condition on same column doesnt return data

• mxy

  SSCarpal Tunnel

  Points: 4024

  More actions

  November 6, 2016 at 6:52 pm

  #315392

  i have a question, below query doesn't return any data. application is allowing filters on same column.

  Is there any other to make it work

  /*-------------------------Drop temp tables-------------------------------------------*/

  IF OBJECT_ID('tempdb..#employee') IS NOT NULL

  DROP TABLE #employee;

  create table #employee

  (

  Employeeid int not null primary key ,

  FirstNm varchar(50) not null,

  LastNm varchar(50) not null

  )

  insert into #employee

  select 1, 'John', 'abc'

  union select 2 , 'William','abc'

  union select 3, 'lary', 'pqr'

  union select 4, 'Linda', 'mvc'

  union select 5, 'Pat', 'pqrs'

  select * from #employee

  where FirstNm = 'John'

  and FirstNm = 'Linda'

• pietlinden

  SSC Guru

  Points: 63198

  More actions

  November 6, 2016 at 7:00 pm

  #1910812

  You need OR, not AND.

  select * from #employee

  where FirstNm = 'John'

  OR FirstNm = 'Linda'

• Eirikur Eiriksson

  SSC Guru

  Points: 182631

  More actions

  November 6, 2016 at 10:00 pm

  #1910814

  There is a flaw in your logic as only if "John" == "Linda" will it ever return anything, which is not going to happen.

  😎

  When searching for multiple values in a column then it is normally easiest to use the IN clause.

  SELECT

  EMP.Employeeid

  ,EMP.FirstNm

  ,EMP.LastNm

  FROM #employee EMP

  WHERE EMP.FirstNm IN ('John','Linda');

• MMartin1

  One Orange Chip

  Points: 27655

  More actions

  November 7, 2016 at 10:58 am

  #1910983

  Ditto,

  First name can be 'John' OR 'Linda' but cannot be both at the same time (which is what a AND condition is implying).

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