Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 9th, 2013, 03:10 PM
Authorized User
 
Join Date: Aug 2004
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Question How to combine queries with two columns

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

Last edited by jtrifts; March 9th, 2013 at 03:11 PM.. Reason: spacing is wrong
 
Old April 4th, 2013, 12:04 AM
Registered User
 
Join Date: Apr 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Smile SELECT firstname, lastname FROM tablename

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

try this













Quote:
Originally Posted by jtrifts View Post
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
 
Old April 16th, 2013, 03:02 PM
Registered User
 
Join Date: Apr 2013
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default joint the table with itself

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
 
Old May 10th, 2013, 01:59 PM
Registered User
 
Join Date: May 2013
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 View Post
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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine two queries snufse SQL Server 2005 6 June 24th, 2008 09:42 PM
Combine sql queries snufse SQL Server 2005 16 June 17th, 2008 03:47 PM
Help: Need to combine multiple IF queries scotts SQL Server 2005 1 April 14th, 2008 07:54 PM
combine 2 queries collie SQL Server 2000 2 November 29th, 2004 03:09 PM
Combine queries lryckman Access VBA 2 May 11th, 2004 11:38 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.