UNION - seeing fields from both tables

[Andy S]

Hi,
A couple of guys on the SQL Server forum solved this one yesterday but
they used a FULL OUTER JOIN, which, little known to me is missing from
Accesss and therefore the JET engine. Can anyone suggest an Access
friendly version? I'd like to be able to see other fields from both
UNIONed tables. For instance :

Positions table:

Book SecurityNumber Description
A 001 Fred
B 002 Jim
B 003 Tom
D 005 Peg
E 006 Sally

Trades Table:

Book SecurityNumber Value
A 001 1000
A 001 3000
C 004 4000
E 006 8000

Output Should look like:

Book SecurityNumber Description Value
A 001 Fred 1000
B 002 Jim
B 003 Tom
C 004 4000
D 005 Peg
E 006 Sally 8000

The linking fields are Book and SecurityNumber via a:

SELECT SecurityNumber, Book FROM Trades UNION
SELECT SecurityNumber, Book from Positions;

Is this possible? Really I'd like to be able to generate a union
query that includes * from both tables and has nulls where a record is
missing from the other table.

Thanks in advance for any help given.

Andy.

[Scott McDaniel]

Try UNION ALL ...

"Andy S" <to***@btintern et.com> wrote in message
news:u1******** *************** *********@4ax.c om...

Hi,
A couple of guys on the SQL Server forum solved this one yesterday but
they used a FULL OUTER JOIN, which, little known to me is missing from
Accesss and therefore the JET engine. Can anyone suggest an Access
friendly version? I'd like to be able to see other fields from both
UNIONed tables. For instance :

Positions table:

Book SecurityNumber Description
A 001 Fred
B 002 Jim
B 003 Tom
D 005 Peg
E 006 Sally

Trades Table:

Book SecurityNumber Value
A 001 1000
A 001 3000
C 004 4000
E 006 8000

Output Should look like:

Book SecurityNumber Description Value
A 001 Fred 1000
B 002 Jim
B 003 Tom
C 004 4000
D 005 Peg
E 006 Sally 8000

The linking fields are Book and SecurityNumber via a:

SELECT SecurityNumber, Book FROM Trades UNION
SELECT SecurityNumber, Book from Positions;

Is this possible? Really I'd like to be able to generate a union
query that includes * from both tables and has nulls where a record is
missing from the other table.

Thanks in advance for any help given.

Andy.

[tom]

Andy -

Here's my guess:

=============== =============== ============
SELECT p.Book, p.SecurityNumbe r, p.Description, t.Value
FROM Positions as p
LEFT JOIN Trades as t
ON (t.Book = p.Book) And (t.SecurityNumb er = p.SecurityNumbe r)
UNION ALL
SELECT t.Book, t.SecurityNumbe r, p.Description, t.Value
FROM Positions as p
RIGHT JOIN Trades as t
ON (t.Book = p.Book) And (t.SecurityNumb er = p.SecurityNumbe r)
WHERE p.Book Is Null
=============== =============== ============

The 1st Select gets all the records in Positions, whether they match
in Trades or not, and the 2nd Select gets all the records in Trades
that are not in Positons.

-Tom

untested air code...

[Andy S]

Thank you so much Tom, it works perfectly. Now to add another 300
JOINs to it ;)

Thanks again,

Andy.

On 18 Nov 2003 14:25:16 -0800, to*@nuws.com (tom) wrote:

Andy -

Here's my guess:

============== =============== =============
SELECT p.Book, p.SecurityNumbe r, p.Description, t.Value
FROM Positions as p
LEFT JOIN Trades as t
ON (t.Book = p.Book) And (t.SecurityNumb er = p.SecurityNumbe r)
UNION ALL
SELECT t.Book, t.SecurityNumbe r, p.Description, t.Value
FROM Positions as p
RIGHT JOIN Trades as t
ON (t.Book = p.Book) And (t.SecurityNumb er = p.SecurityNumbe r)
WHERE p.Book Is Null
============== =============== =============

The 1st Select gets all the records in Positions, whether they match
in Trades or not, and the 2nd Select gets all the records in Trades
that are not in Positons.

-Tom

untested air code...