By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,312 Members | 1,771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,312 IT Pros & Developers. It's quick & easy.

Joining a UNION to another Select

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Nov 12 '05 #2

P: n/a
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.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.