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

Do UNION queries inherently force UNIQUE records by their own nature?

P: n/a
MLH
Consider having tblCorrespondence, then copying & pasting
it to tblCorrespondence1 - such that they are identical. Then
consider the following UNION SELECT statement...

SELECT tblCorrespondence.CorrespID, tblCorrespondence.VehicleJobID,
tblCorrespondence.OutDate, tblCorrespondence.OutType,
tblCorrespondence.OutProcessor, tblCorrespondence.InDate,
tblCorrespondence.InRefDate, tblCorrespondence.InType,
tblCorrespondence.InProcessor, tblCorrespondence.ToWhom,
tblCorrespondence.CorrespTDStamp, tblCorrespondence.UserID,
tblCorrespondence.Tracked
FROM tblCorrespondence
WHERE (((tblCorrespondence.VehicleJobID)=901) AND
((tblCorrespondence.OutType)="00"))

UNION SELECT tblCorrespondence1.CorrespID,
tblCorrespondence1.VehicleJobID, tblCorrespondence1.OutDate,
tblCorrespondence1.OutType, tblCorrespondence1.OutProcessor,
tblCorrespondence1.InDate, tblCorrespondence1.InRefDate,
tblCorrespondence1.InType, tblCorrespondence1.InProcessor,
tblCorrespondence1.ToWhom, tblCorrespondence1.CorrespTDStamp,
tblCorrespondence1.UserID, tblCorrespondence1.Tracked
FROM tblCorrespondence1
WHERE (((tblCorrespondence1.VehicleJobID)=901) AND
((tblCorrespondence1.OutType)="00"));

Knowing full well that both tables contain 1 record meeting the
specified criteria (record ID #69203) - why do I see only a single
row in the query output?

If I change the criteria specified for tblCorrespondence1 to
WHERE (((tblCorrespondence1.VehicleJobID)=902) AND
((tblCorrespondence1.OutType)="00")); (replacing 901 with 902)
I see 2 rows in the output. I'm not saying I have a fruitful purpose
for pulling the same record number from 2 identical tables

I even tried the following. I still get the same results - a single
record in the output. Anybody know why?

SELECT tblCorrespondence.CorrespID AS ONE,
tblCorrespondence.VehicleJobID AS TWO, tblCorrespondence.OutDate AS
THREE, tblCorrespondence.OutType AS FOUR,
tblCorrespondence.OutProcessor AS FIVE, tblCorrespondence.InDate AS
SIX, tblCorrespondence.InRefDate AS SEVEN, tblCorrespondence.InType AS
EIGHT, tblCorrespondence.InProcessor AS NINE, tblCorrespondence.ToWhom
AS TEN, tblCorrespondence.CorrespTDStamp AS ELEVEN,
tblCorrespondence.UserID AS TWELVE, tblCorrespondence.Tracked AS
THIRTEEN
FROM tblCorrespondence
WHERE (((tblCorrespondence.VehicleJobID)=901) AND
((tblCorrespondence.OutType)="00"))

UNION SELECT tblCorrespondence1.CorrespID AS ONE,
tblCorrespondence1.VehicleJobID AS TWO, tblCorrespondence1.OutDate AS
THREE, tblCorrespondence1.OutType AS FOUR,
tblCorrespondence1.OutProcessor AS FIVE, tblCorrespondence1.InDate AS
SIX, tblCorrespondence1.InRefDate AS SEVEN, tblCorrespondence1.InType
AS EIGHT, tblCorrespondence1.InProcessor AS NINE,
tblCorrespondence1.ToWhom AS TEN, tblCorrespondence1.CorrespTDStamp AS
ELEVEN, tblCorrespondence1.UserID AS TWELVE,
tblCorrespondence1.Tracked AS THIRTEEN
FROM tblCorrespondence1
WHERE (((tblCorrespondence1.VehicleJobID)=901) AND
((tblCorrespondence1.OutType)="00"));

Jan 27 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
MLH <CR**@NorthState.netwrote in
news:68********************************@4ax.com:
Consider having tblCorrespondence, then copying & pasting
it to tblCorrespondence1 - such that they are identical. Then
consider the following UNION SELECT statement...

Knowing full well that both tables contain 1 record meeting the
specified criteria (record ID #69203) - why do I see only a single
row in the query output?

If I change the criteria specified for tblCorrespondence1 to
WHERE (((tblCorrespondence1.VehicleJobID)=902) AND
((tblCorrespondence1.OutType)="00")); (replacing 901 with 902)
I see 2 rows in the output. I'm not saying I have a fruitful
vpurpose
for pulling the same record number from 2 identical tables

I even tried the following. I still get the same results -
single
record in the output. Anybody know why?
The drafters of the SQL language figured correctly that the
majourity of UNION queries would use the DISTINCT Clause, so that is
what is the default, contrary to a normal SELECT query has ALL as
the default.

Like most languages, not specifying ALL|DISTINCT results in the
default being applied.

Override the default? Just add the ALL Keyword after the UNION
SELECT statement.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Jan 27 '08 #2

P: n/a
MLH
Cool. Many thx.
Jan 28 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.