Using combination of join and union in query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Using combination of join and union in query

Author

Topic

leonardr
Starting Member

1 Post

Posted - 2003-03-30 : 05:31:00

Hi,

I'm trying to build a join out of two or more different tables.
At first i use a separate query wich makes use of one or more UNION statements:

Query_union:

SELECT Koppeling,UID, data, testveld
FROM waarden
UNION SELECT Koppeling,UID, data, null as testveld
FROM waarden2;

In another table called tbl_xml there is a field that matches the content of UID
Using this UID which is u unique identifier , i would like to join the content of the query above with tbl_xml

When i adress to the queryname there is no problem

SELECT xml.Path, xml.UID, Query_union.DATA
FROM xml left JOIN Query_union ON xml.UID= Query_union.UID;

Now i want to make a query that does the union AND the join. The reason is: I don't want to use two queries because te number of tables in the union might differ
Only i'm not succeeding .

Leon

Bambola
Posting Yak Master

103 Posts

Posted - 2003-03-30 : 08:12:01

SELECT Koppeling,UID, data, testveld, xml.Path, xml.UID
FROM (
SELECT Koppeling,UID, data, testveld
FROM waarden
UNION ALL -- (assuming no duplication)
SELECT Koppeling,UID, data, null as testveld
FROM waarden2
) Query_union
LEFT JOIN xml ON xml.UID= Query_union.UID

Bambola.

Edited by - bambola on 03/30/2003 08:45:26

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-30 : 11:40:24

Bambola --

In Access, you can't do that kind of query; you need to break it up into a "query of a query", basically exactly what leanardr has already done.

leonardr --

quote:

The reason is: I don't want to use two queries because te number of tables in the union might differ

Can you explain what you mean by this? Your current solution looks fine, I am not sure what you are saying by this ...

- Jeff

Bambola
Posting Yak Master

103 Posts

Posted - 2003-03-30 : 12:46:56

quote:

Bambola --
In Access, you can't do that kind of query; you need to break it up into a "query of a query", basically exactly what leanardr has already done.
- Jeff

Opss... did not notice we are talking Access....

Bambola.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-03-31 : 02:05:03

Jeff - it seems that more and more people are missing out on their daily dose of "Wiener Melange", and the after-effects are appearing on the Access forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources