Re: Do UNION queries inherently force UNIQUE records by their own nature?
am 27.01.2008 21:35:34 von Bob Quintal
MLH wrote in
news:68rpp3dob6m847kfaqt6etbji973bdjfve@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
Do UNION queries inherently force UNIQUE records by their own nature?
am 27.01.2008 21:59:26 von 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"));