CODEWITHSUNDEEP
sqloracle-databaseunion
Adam Halegua
Adam Halegua

Reputation: 185

Multiple column Union Query without duplicates

I'm trying to write a Union Query with multiple columns from two different talbes (duh), but for some reason the second column of the second Select statement isn't showing up in the output. I don't know if that painted the picture properly but here is my code:

Select empno, job
From EMP
Where job = 'MANAGER'
Union
  Select empno, empstate
  From EMPADDRESS
  Where empstate = 'NY'
  Order By empno

The output looks like:

EMPNO   JOB
4600    NY
5300    MANAGER
5300    NY
7566    MANAGER
7698    MANAGER
7782    MANAGER
7782    NY
7934    NY
9873    NY

Instead of 5300 and 7782 appearing twice, I thought empstate would appear next to job in the output. For all other empno's I thought the values in the fields would be (null). Am I not understanding Unions correctly, or is this how they are supposed to work?

Thanks for any help in advance.

Upvotes: 4

Views: 29329

Answers (5)

GKV
GKV

Reputation: 501

this one works in oracle..by using union ..here inner query will fetch out the all the columns after that grouping with empno and rest of the columns is string concatenated 

  select EMPNO
        ,wm_concat(job) job
        ,wm_concat(EMPSTATE) EMPSTATE 
  from 
  ( select EMPNO,job,'' as EMPSTATE  from EMP Where job ='MANAGER'
    union  select EMPNO,'' as job, EMPSTATE from EMPADDRESS Where empstate ='NY'
  )
 group by EMPNO order by 1

Upvotes: 0

NYCdotNet
NYCdotNet

Reputation: 4637

Bluefeet has the correct answer.

Think of joins as combining tables horizontally - you're adding more columns to the original query with each table you join.

Think of unions as stacking record sets vertically - you're adding extra rows to the same set of columns.

Upvotes: 1

DazzaL
DazzaL

Reputation: 21973

i think you meant to write is as a join instead?

ie if you wanted empstate to be null for those employee's not in NY. 

select empno, job, empstate
from emp e
     left outer join empaddress a
                  on a.empno = e.empno
                 and e.empstate = 'NY'
where e.job = 'MANAGER';

Upvotes: 0

StevieG
StevieG

Reputation: 8709

You need a JOIN for this..

Select e.empno, e.job, ea.empstate
From EMP e LEFT OUTER JOIN EMPADDRESS ea ON e.empno = ea.empno
Where e.job = 'MANAGER'
And ea.empstate = 'NY'
Order By e.empno

UNION is for taking 2 result sets with the same column names and merging them into one. In your example, its lumping column 2 (job and empstate) together, and taking the name from the first select.

Upvotes: 0

Taryn
Taryn

Reputation: 247620

If you want the data in a separate column you will want a JOIN not a UNION:

Select e.empno, e.job, a.empstate
From EMP e
left join EMPADDRESS a
    on e.empno = a.empno
Where job = 'MANAGER'
    AND empstate = 'NY'
Order By e.empno

A UNION combines the two results into a single set but the data is listed in the same columns. So basically they are placed on top of one another:

select col1, col2, 'table1' as src
from table1
union all
select col1, col2, 'table2' as src
from table2

Will result in:

col1 | col2 | src
t1   | t1   | table1
t2   | t2   | table2

If you want to have the data in a separate column which is sounds like you do then you will use a join of the tables.

Upvotes: 5

Related Questions