Reputation: 7
Hi everyone, am new to SQL and am encountering the following problem when i execute a simple select command. Below is the codes:
select cheffname
from speciality
where speciality = 'Japanese Cuisine';
Daniel
Pamela
Sarah
select cheffname
from speciality
where speciality = 'Chinese Cuisine';
Daniel
Jerry
select cheffname
from speciality
where speciality = 'Chinese Cuisine' and speciality = 'Japanese Cuisine';
no rows selected
As you can see, I would expect the 3rd SQL Query to return Daniel
but it doesn't. What am I doing wrong?
Upvotes: 0
Views: 51
Reputation: 91
you got 'no rows selected' because this query,
select cheffname from speciality where speciality = 'Chinese Cuisine' and speciality = 'Japanese Cuisine';
returns rows when both conditions in where clause are true,in your case its never possible because you wrote 2 conditions on same column speciality.I hope u understand it and u can get desired output by several ways one of them is,
select cheffname from speciality where speciality = 'Chinese Cuisine'
union
select cheffname from speciality where speciality = 'Japanese Cuisine'
Upvotes: 0
Reputation: 1269803
I believe that you want:
select cheffname
from speciality
where speciality in ('Chinese Cuisine', 'Japanese Cuisine')
group by cheffname
having count(*) = 2;
Upvotes: 1
Reputation: 31656
INTERSECT
is one option.
SELECT cheffname
FROM speciality
WHERE speciality = 'Chinese Cuisine'
INTERSECT
SELECT cheffname
FROM speciality
WHERE speciality = 'Japanese Cuisine'
Upvotes: 0
Reputation: 14389
Try this:
SELECT cheffname
FROM speciality
WHERE cheffname IN (
SELECT cheffname
FROM speciality
WHERE speciality = 'Japanese Cuisine'
)
AND cheffname IN (
SELECT cheffname
FROM speciality
WHERE speciality = 'Chinese Cuisine'
)
Upvotes: 0