Concatenating Queries

Question

0.00/5 (No votes)

See more:

I need to join two queries together but they don't have exactly the same structure..

the structure of query one is : name, department, totalsalesafrica

the sturcture of query two is : name, department, totalsaleseurope

Data query one..

fred dvd's 250
dave records 300
don sheetmusic 150

Data query two

fred dvd's 100
dave records 350
denise dvd's 200

Resultant query

fred dvd's 250 100
dave records 300 350
don sheetmusic 150 0
denise dvd's 0 200

Thanks

Scott

Posted 14-Jun-10 6:18am

sadderson

Updated 14-Jun-10 6:25am

Henry Minute

v2

Add a Solution

2 solutions

Solution 2

You could use inner selects. I dont' know how your tables are setup but it would be something like this:

SELECT NAME, DEPT, 
(SELECT SUM(SALES) FROM TblSales WHERE TblSales.REGION='AFRICA' AND TblSales.NAME=TblCustomers.NAME AND TblSales.DEPT=TblCustomers.DEPT) AS AfricaSales, 
(SELECT SUM(SALES) FROM TblSales WHERE TblSales.REGION='EUROPE' AND TblSales.NAME=TblCustomers.NAME AND TblSales.DEPT=TblCustomers.DEPT) AS EuropeSales 
FROM TblCustomers

Posted 14-Jun-10 7:54am

Kschuler

Comments

sadderson 14-Jun-10 14:43pm

Thankyou for your response.. your idea is great, and looks like it would work for what I supplied, in order to get the information I have above there is a complicated query already involved.. so the union option seems better for this peticular case (althought with the info I supplied you had no way of knowing that)... great i will use this style in the future..

Thanks again... Scott

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Accepted Answer · 2010-06-14T06:25:00

Solution 1

You could try

<br />
select name,department,totalsalesafrica as Salesafrica, 0 as SalesEurope from table1<br />
UNION<br />
select name,department,0 as Salesafrica, totalsaleseurope as SalesEurope from table2

Posted 14-Jun-10 6:25am

Abhinav S

v2

Comments

sadderson 14-Jun-10 14:24pm

That worked super... I thought of that but didn't have the confidence to write it until you brought it up again... thanks for the help in order to get the totals on the same line you need to combine the union in another nested query as just the union gives two records for name and department but if you sum(salesafrica) and sum(saleseurope) once you have the union then i have what I need...

thanks again.

Scott

Abhinav S 15-Jun-10 0:38am

Your welcome. :)