Combining ALL data from two tables 3

[mrtauntaun]

I have two tables. Each table shows a request # and another number field. Some, but not all, data between these two tables overlap in the request #. What I want my end result to be is one display as follows:

Reqnum [Table1.Numfield] [Table2.Numfield]

So, show me all request #'s from both tables and group by Reqnum (So that I only see 1 Reqnum, and both Table1 and 2 numfields have an entry). Some of the numfields will return a null value (for those few records that do not overlap), and that is fine.
Am I explaining that well enough? I can't for the life of me figure this out, any help would be appreciated, thanks

 

[Golom]

Try

Select A.RequestNum, A.NumField, B.NumField
From tbl1 As A LEFT JOIN tbl2 As B
     ON A.RequestNum = B.RequestNum

UNION

Select B.RequestNum, A.NumField, B.NumField
From tbl1 As A RIGHT JOIN tbl2 As B
     ON B.RequestNum = A.RequestNum

The use of UNION (rather than UNION ALL) will eliminate duplicate records in the two sub-queries.

 

[lynchg]

First create a union query to create one big set of all of the records from both tables:

Select Distinct Reqnum, Numfield
From Table1
Union
Select Distinct Reqnum, Numfield
From Table2

Call this query qUnion, create another query with left joins from qUnion to each of your two tables:

Select Distinct qu.Reqnum, t1.Numfield, t2.Numfield
From qUnion qu
Left Join Table1 t1 On qu.Reqnum = t1.Reqnum
Left Join Table2 t2 On qu.Reqnum = t2.Reqnum

This second query should give you the results you want.

HTH

 

[lynchg]

Golom, I haven't seen your approach before. It looks like it would produce the same result as my approach but with one less step.

I came up with my solution when I needed to produce a similar result as mrtauntaun was looking for.

I am going to try rewriting mine with your approach because, although mine worked, I have never been satisfied with the performance. I actually use 4 queries that get unioned together, and then I left joined the union to each of the 4 original queries. It goes against some large data sets and takes about 2 min to execute.

 

[mrtauntaun]

I would like to thank you both very much. Golom's statement when input worked exactly the way I wanted it. However, Lynchg, you work more my speed, a query to make a query, so with your explanation, I was able to understand what Golom was saying. So thanks to both of you, I have the results I need AND I understand how it works
Thanks again!

 

[lynchg]

In trying to incorporate golom's approach it looks like it would be even more complicated than mine. I would have to create outer joins between each pair of queries. That would be 12 separate subqueries.

It was good to see the different approach though, I'll remember that one.