Union queries 3

[villica]

Hi everyone

What is the easier way of combining 4 queries

There are the outputs


Query1

Class description actualmtd actualprize

Query 2

Class descripption ActualYtd ActualYTdprize


Query 3

Class description PrioryearMtD PriorYearMtdPrize


Query 4
Class description PrioryearYTD PriorYearYTdPrize



I thought a union query woud work because query 1 may return only 2 clasess and query 4 may return all of them. The problem is that I need to keep the column names coming from every query such actualmtd, actualprize, actualytd etc

Any suggestions please


villica

 

[Remou]

It seems to me that what you need is an ordinary enough query with left joins. You may wish to read this:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins/

 

[villica]

a left join would not work, lets say

table 1
class 1
2
3
4


table 2
class 5
1
2
3
4


if I do a left join it will get class 5.
if I do a righ join I would get class 5

but the problem is next month I may get more classess in table 1 and less classes in table 2



villica

 

[lespaul]

but do you have a list of all potential classes? if so then it doesn't matter, do a left join into that table...maybe if you provide some sample data we'd be able to help you get it all in one query....


Leslie

Have you met Hardy Heron?

 

[randy700]

One way...

SELECT ClassDescription, ActualMtd, ActualPrice, "" As ActualYtd, "" As ActualYtdPrize, "" As PriorYearMtd, "" As PriorYearMtdPrize, "" As PriorYearYtd, "" As PriorYearYtdPrize FROM TableName
UNION
SELECT ClassDescription, "" As ActualMtd, "" As ActualPrice, ActualYtd, ActualYtdPrize, "" As PriorYearMtd, "" As PriorYearMtdPrize, "" As PriorYearYtd, "" As PriorYearYtdPrize FROM TableName
UNION
SELECT ClassDescription, "" As ActualMtd, "" As ActualPrice, "" As ActualYtd, "" As ActualYtdPrize, PriorYearMtd, PriorYearMtdPrize, "" As PriorYearYtd, "" As PriorYearYtdPrize FROM TableName
UNION
SELECT ClassDescription, "" As ActualMtd, "" As ActualPrice, "" As ActualYtd, "" As ActualYtdPrize, "" As PriorYearMtd, "" As PriorYearMtdPrize, PriorYearYtd, PriorYearYtdPrize FROM TableName

Randy

 

[villica]

thank you so much Randy, now I have to work on the table layout for my report and maybe you can help me. I will open anothe post anyhow but I thoug ai put it here

the output comes out as follow from the uniont query

class description actualmtd actualytd priormtd lastyed
abc 10
abc 10
abc 9
abc 10

I need to make a table taht will put everyting in one line

villica

 

[PHV]

SELECT [class description], Max(actualmtd), Max(actualytd), Max(priormtd), Max(lastyed)
FROM yourUnionQuery
GROUP BY [class description]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[villica]

Thank you so much PHV

it works beauitfully!!

villica