newcoder69
newcoder69

Reputation: 7

Issue with Oracle SQL where statement

Hi everyone, am new to SQL and am encountering the following problem when i execute a simple select command. Below is the codes:

SQL:

select cheffname
from speciality
where speciality = 'Japanese Cuisine';

Output:

Daniel
Pamela
Sarah

SQL:

select cheffname
from speciality
where speciality = 'Chinese Cuisine';

Output:

Daniel
Jerry

SQL

select cheffname
from speciality
where speciality = 'Chinese Cuisine' and speciality = 'Japanese Cuisine';

Output:

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

Answers (4)

karun
karun

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

Gordon Linoff
Gordon Linoff

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

Kaushik Nayak
Kaushik Nayak

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

apomene
apomene

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

Related Questions