CODEWITHSUNDEEP
sqloracle-database
user3553846
user3553846

Reputation: 342

SELECT clause using INTERSECT

I have a table DEPTLOC like this:

//DEPTLOC
DNAME              CITY
------------------------
RESEARCH          BOSTON
   IT             LONDON
 SCIENCE          BOSTON
SEARCHING         NEWYORK

I used the following query:

SELECT CITY FROM DNAME WHERE DNAME='RESEARCH'
INTERSECT
SELECT CITY FROM DNAME WHERE DNAME='SCIENCE'

So this returns a result like:

CITY
---------
BOSTON

But how should I change the code so that if any result is found, it will display NO, else it will display YES

Output should be something like this:

RESULT
---------
YES

or

RESULT
---------
NO

Upvotes: 4

Views: 80

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

What you want to do is counting your results.

SELECT
  CASE WHEN COUNT(*) = 0 THEN 'Yes' ELSE 'No' END
FROM
(
  SELECT CITY FROM DNAME WHERE DNAME='RESEARCH'
  INTERSECT
  SELECT CITY FROM DNAME WHERE DNAME='SCIENCE'
);

There are of course other ways to achieve the same result. This one demonstrates how to use your INTERSECT query to do it.

Upvotes: 1

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

You can do it like below using COUNT() and DECODE/CASE 

SELECT DECODE(COUNT(1),0,'NO','YES') AS RESULT FROM 
(
  SELECT CITY FROM DNAME WHERE DNAME='RESEARCH'
  INTERSECT
  SELECT CITY FROM DNAME WHERE DNAME='SCIENCE'
)

Upvotes: 2

Related Questions