I have a table named "Student".
Student
id | name | age
1 | john | 10
2 | jack | 10
3 | jerry| 10
I wanna select 1 and 2 rows. I wrote that Select * from Student where name=john and name=jack
But return "Empty Set".
How do i do it. Help me.
4 Answers
select *
from student
where name in ('john', 'jack')
Or
select *
from student
where name = 'john'
or name = 'jack'
You need an OR rather than an AND.
Whatever conditions your write, it checks them all against each record. As no single record has both name = 'john' AND name = 'jack' they all fail.
If, instead, you use OR...
- The 1st record yields TRUE OR FALSE which is TRUE.
- The 2nd record yields FALSE OR TRUE which is TRUE.
- The 3rd record yields FALSE OR FALSE which is FALSE.
Select * from Student where name='john' OR name='jack'
Or, using a differnt way of saying it all...
SELECT * FROM Student WHERE name IN ('john', 'jack')
Use single quotes to surround your values, plus use and instead of or:
where name='john' or name = 'jack'
try this one.
declare @names varchar(100)
set @names='john,jack'
Select * from Student
where charIndex(',' + rtrim(cast(name as nvarchar(max))) + ',',',' +isnull(@names,name) +',') >0
