Joining a UNION to another Select

[Shaggy Dragon]

Hi there, been looking for a solution to this for some time now. I've
a UNION query that produces a table called AllSecurities:

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

I'd really like to show is all the fields from the Positions table,
but these don't exist in the Trades table, so they can't be included
in the UNION (as far as I know). Is it possible to link this to a:

SELECT Positions.* FROM Position;

by joining the 1st query to the 2nd by the use of:

LEFT JOIN Positions ON (AllSecurities.Book = Positions.Book) AND
(AllSecurities.SecurityNumber = Positions.SecurityNumber);

Thanks in advance for any advice given.

Andy.

[Douglas J. Steele]

If you really need to, you can pad the select of Trades with dummy fields
(either Nulls, or "" for the text fields and 0 for the numeric ones):

SELECT SecurityNumber, Book, ATextField, ANumericField, AnotherTextField
FROM Positions
UNION
SELECT SecurityNumber, Book, NULL AS ATextField, NULL AS ANumericField, NULL
AS AnotherTextField FROM Positions

or

SELECT SecurityNumber, Book, ATextField, ANumericField, AnotherTextField
FROM Positions
UNION
SELECT SecurityNumber, Book, "" AS ATextField, 0 AS ANumericField, "" AS
AnotherTextField FROM Positions

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"Shaggy Dragon" <Sh***********@his.cave> wrote in message
news:b5********************************@4ax.com...

Hi there, been looking for a solution to this for some time now. I've
a UNION query that produces a table called AllSecurities:

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

I'd really like to show is all the fields from the Positions table,
but these don't exist in the Trades table, so they can't be included
in the UNION (as far as I know). Is it possible to link this to a:

SELECT Positions.* FROM Position;

by joining the 1st query to the 2nd by the use of:

LEFT JOIN Positions ON (AllSecurities.Book = Positions.Book) AND
(AllSecurities.SecurityNumber = Positions.SecurityNumber);

Thanks in advance for any advice given.

Andy.

[Shaggy Dragon]

On Sun, 16 Nov 2003 23:23:29 GMT, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

If you really need to, you can pad the select of Trades with dummy fields
(either Nulls, or "" for the text fields and 0 for the numeric ones):

SELECT SecurityNumber, Book, ATextField, ANumericField, AnotherTextField
FROM Positions
UNION
SELECT SecurityNumber, Book, NULL AS ATextField, NULL AS ANumericField, NULL
AS AnotherTextField FROM Positions

or

SELECT SecurityNumber, Book, ATextField, ANumericField, AnotherTextField
FROM Positions
UNION
SELECT SecurityNumber, Book, "" AS ATextField, 0 AS ANumericField, "" AS
AnotherTextField FROM Positions

Thanks for the suggestion, but there seems to be a problem:

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

returns 676 records whilst:

SELECT SecurityNumber, Book, SecurityDescription FROM Positions UNION
SELECT SecurityNumber, Book, NULL As SecurityDescription FROM Trades

Returns 928 records. There are a lot of duplicates present in the
table. I replaced NULL with "" with exactly the same result. Nearly
there, any other ideas I can try?

Thanks,

Andy.