UNION - selecting 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
Access 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
A 001 Fred 3000
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.

 

[Brian Camire]

You might try something like this:

1. Use the UNION query you posted (say it's named "Query1") that returns
the distinct combinations of Book and SecurityNumber from both tables.

2. Create another query that OUTER JOINs the first query to both tables.
The SQL might look something like this:

SELECT
Query1.Book,
Query1.SecurityNumber,
Trades.Description,
Positions.Value
FROM
(Query1
LEFT JOIN
Trades
ON
(Query1.Book = Trades.Book)
AND
(Query1.SecurityNumber = Trades.SecurityNumber))
LEFT JOIN
Positions ON
(Query1.Book = Positions.Book)
AND
(Query1.SecurityNumber = Positions.SecurityNumber);

 

[Dale Fye]

Andy,

Use a union query. The first half of this question returns all the
records from P and their matching records in T. The second part
returns all the records from T that don't have a matching value in P.

SELECT P.Book, P.SecurityNumber, P.Description,
T.Value
FROM Positions P
LEFT JOIN Trades T
ON P.Book = T.Book AND P.SecurityNumber = P.SecurityNumber
UNION ALL
SELECT T.Book, T.SecurityNumber, NULL, T.Value
FROM Trades T
LEFT JOIN Positions P
ON T.Book = P.Book AND T.SecurityNumber = P.SecurityNumber
WHERE P.Book IS NULL

--
HTH

Dale Fye


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
Access 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
A 001 Fred 3000
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.

 

[Andy S]

Hi Dale, thanks for the response, I tried this in Access XP and I get
"Join Expression Not Supported". Am I doing something wrong?

Andy.