UNION or UNION ALL query question

[Olga]

Hello,
I have a problem regarding combining various cross tab queries into
one. The problem comes in when I want only two columns from each cross
tab query combined, while the third column I would like added to the
table without combinig it with another. I'm not great at explaining
these things so:

Query 1
ID NAME Activity
ol678 Olga 533100066
bl574 Andrew 533100066
db563 Debra 533100054

Query 2
ID NAME AnotherActivity
ol678 Olga 533100034
bl574 Andrew 533100023
mh435 Mike 533100956
db563 Debra 533100054

Combine these two to show:
Result
ID NAME Activity AnotherActivity
ol678 Olga 533100066 533100034
bl574 Andrew 533100066 533100023
mh435 Mike 533100956
db563 Debra 533100054 533100054


Is this type of UNION query possible?
Please let me know if you have any suggestions.
Thank you!
Olga

 

[giorgio rancati]

Hi Olga,
try this
----
SELECT Qu.Id,
Qu.Name,
Query1.Activity,
Query2.AnotherActivity
FROM ((SELECT Id,Name
FROM Query1
UNION
SELECT Id,Name
FROM Query2)AS Qu
LEFT JOIN Query1 ON Qu.Id=Query1.Id)
LEFT JOIN Query2 ON Qu.Id=Query2.Id

 

[Guest]

The result that you wish is not a from a Union query. The UNION or UNION ALL
would display the following:

ID NAME Activity
ol678 Olga 533100066
bl574 Andrew 533100066
db563 Debra 533100054
ol678 Olga 533100034
bl574 Andrew 533100023
mh435 Mike 533100956
db563 Debra 533100054

Activity would just be the column name in the first sql statement.

Looks to me like you just need to join the two queries by ID and NAME in a
third query. You may need a left join as Mike isn't in both queries.