CODEWITHSUNDEEP
sqloracle-database
user3553846
user3553846

Reputation: 342

SELECT using UNION Clause

For example . i having these 2 table

//DEPARTMENT
DNAME          CITY
--------------------
RESEARCH       BOSTON
   IT          CITY
 SCIENCE       DALLAS
RESEARCH       DALLAS

So i using this statement

(SELECT DNAME FROM DEPARTMENT WHERE CITY='BOSTON')
UNION
(SELECT DNAME FROM DEPARTMENT WHERE CITY='DALLAS'):

Using this statement i get this output

DNAME
-----
RESEARCH
SCIENCE

But my question is to choose either in 'BOSTON' or 'DALLAS' But not in both, my output should be like this

DNAME
--------
SCIENCE

What should i change in order to get the correct output

Upvotes: 2

Views: 97

Answers (4)

redsoxlost
redsoxlost

Reputation: 1235

You can use ROWNUM psudocolumn , see the Fidde here

Upvotes: -1

Olesya Razuvayevskaya
Olesya Razuvayevskaya

Reputation: 1168

The problem is: UNION automatically removes all the duplicates. Since, DNAME is the only returned column, it will remove duplicates

Edit For distinct selection of either DNAME in 'DALLAS' or 'BOSTON', something like this:

    SELECT DNAME FROM DEPARTMENT 
                 WHERE (CITY='BOSTON' and CITY<>'DALLAS')

Upvotes: 0

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

You should use INTERSECT and MINUS (for Oracle):

-- At least at Boston or at Dallas 
(SELECT DNAME 
   FROM DEPARTMENT 
  WHERE CITY = 'DALLAS'

UNION

 SELECT DNAME 
   FROM DEPARTMENT 
  WHERE CITY = 'BOSTON')

MINUS

-- At Boston and at Dallas
(SELECT DNAME 
   FROM DEPARTMENT 
  WHERE CITY = 'DALLAS'

INTERSECT

 SELECT DNAME 
   FROM DEPARTMENT 
  WHERE CITY = 'BOSTON')

Since UNION adds subqueries up while EXCEPT/MINUS subtracts them

Upvotes: 3

Noel
Noel

Reputation: 10525

You can filter out the dnames first and then use group by to select only those dnames that are present in one city only.

select dname
  from department
 where city in ('BOSTON', 'DALLAS')
 group by dname
having count(city) = 1;

Upvotes: 1

Related Questions