How to combine queries with two columns

jtrifts · March 9th, 2013, 03:10 PM

I want to combine some queries together into one rather than using union possibly.
Currently I'm doing something like this

select firstname, lastname, 'acc' as 'department' where dept = 'acc'
union
select firstname, lastname, 'man' as 'department' where dept = 'man'

so I get
firstname lastname department
--------------- -------------- -----------------
john doe acc
john doe man

is there a way to rewrite this so I can get something like

firstname lastname department department2
--------------- -------------- ----------------- ------------------
john doe acc man

krsna · April 4th, 2013, 12:04 AM

SELECT firstname, lastname FROM tablename
UNION
WHERE dept='acc' OR dept='man'

try this

Quote:

Originally Posted by jtrifts

I want to combine some queries together into one rather than using union possibly.
Currently I'm doing something like this

select firstname, lastname, 'acc' as 'department' where dept = 'acc'
union
select firstname, lastname, 'man' as 'department' where dept = 'man'

so I get
firstname lastname department
--------------- -------------- -----------------
john doe acc
john doe man

is there a way to rewrite this so I can get something like

firstname lastname department department2
--------------- -------------- ----------------- ------------------
john doe acc man

tgeleijn · April 16th, 2013, 03:02 PM

Hi

you want 2 rows on 1 resultline, which is not really sql's nature
I would try to join the table with itself on the firstname and lastname
the resulting table will have 2 department fields

hope it helps you

I would myself do this in java. Sort the table on firstname, lastname, read the table, gather the departments in an array, and when the combination firstname. lastname changes, print all the departments. This will allow you have multiple departments on 1 line.

Cheers
Theo

sduncan · May 10th, 2013, 01:59 PM

Try something like this:

Code:

SELECT
    FIRSTNAME,
    LASTNAME,
    CASE
       WHEN Q1.DEPT LIKE 'ACC' THEN 'ACC' 
       WHEN Q2.DEPT LIKE 'MAN' THEN 'MAN'
       ELSE ''
    END AS DEPARTMENT
FROM TABLE1 Q1
    LEFT OUTER JOIN TABLE2 Q2 ON Q1.FIELD = Q2.FIELD

Quote:

Originally Posted by jtrifts

I want to combine some queries together into one rather than using union possibly.
Currently I'm doing something like this

select firstname, lastname, 'acc' as 'department' where dept = 'acc'
union
select firstname, lastname, 'man' as 'department' where dept = 'man'

so I get
firstname lastname department
--------------- -------------- -----------------
john doe acc
john doe man

is there a way to rewrite this so I can get something like

firstname lastname department department2
--------------- -------------- ----------------- ------------------
john doe acc man