This works, but should it??

[Guest]

SELECT DISTINCT LOCAL_Sales.SaleID, LOCAL_Sales.Date, LOCAL_Sales.CardRecordID, LOCAL_Sales.InvoiceStatusI
FROM LOCAL_Sale

UNION SELECT DISTINCT HISTORY_Sales.SaleID, HISTORY_Sales.Date, HISTORY_Sales.CardRecordID, HISTORY_Sales.InvoiceStatusI
FROM HISTORY_Sales LEFT JOIN LOCAL_Sales ON HISTORY_Sales.SaleID = LOCAL_Sales.SaleI

WHERE LOCAL_Sales.SaleID Is Null

This returns exactly every reord in "LOCAL_Sales" as well as every record in "HISTORY_Sales" not duplicated in "LOCAL_Sales". My question is: how is the SaleID field in the WHERE clause interpreted as applying only to the JOIN clause and not the SELECT clause

Cheer

ALi

 

[Ken Snell]

Not sure what you're asking? The WHERE clause is applied to each record
returned by the query, of which the JOIN is a major factor. It's working
just the way you expect.

--

Ken Snell
<MS ACCESS MVP>

SELECT DISTINCT LOCAL_Sales.SaleID, LOCAL_Sales.Date,

LOCAL_Sales.CardRecordID, LOCAL_Sales.InvoiceStatusID

FROM LOCAL_Sales

UNION SELECT DISTINCT HISTORY_Sales.SaleID, HISTORY_Sales.Date,

HISTORY_Sales.CardRecordID, HISTORY_Sales.InvoiceStatusID

FROM HISTORY_Sales LEFT JOIN LOCAL_Sales ON HISTORY_Sales.SaleID = LOCAL_Sales.SaleID

WHERE LOCAL_Sales.SaleID Is Null;

This returns exactly every reord in "LOCAL_Sales" as well as every record

in "HISTORY_Sales" not duplicated in "LOCAL_Sales". My question is: how is
the SaleID field in the WHERE clause interpreted as applying only to the
JOIN clause and not the SELECT clause?

 

[Guest]

Thanks for answering Ken

On the contrary, I (1/2) expected the WHERE clause to apply, like you say, "to each record returned". But in this case it isn't applied to those records returned by the "SELECT...<fields>...FROM LOCAL_Sales" statement. Is this because it comes before the UNION keyword? Does the UNION effectively begin a new query?

Thanks

Ali

----- Ken Snell wrote: -----

Not sure what you're asking? The WHERE clause is applied to each record
returned by the query, of which the JOIN is a major factor. It's working
just the way you expect.

--

Ken Snell
<MS ACCESS MVP>

SELECT DISTINCT LOCAL_Sales.SaleID, LOCAL_Sales.Date,

LOCAL_Sales.CardRecordID, LOCAL_Sales.InvoiceStatusID

FROM LOCAL_Sales

HISTORY_Sales.CardRecordID, HISTORY_Sales.InvoiceStatusID

FROM HISTORY_Sales LEFT JOIN LOCAL_Sales ON HISTORY_Sales.SaleID = LOCAL_Sales.SaleID

in "HISTORY_Sales" not duplicated in "LOCAL_Sales". My question is: how is
the SaleID field in the WHERE clause interpreted as applying only to the
JOIN clause and not the SELECT clause?

 

[John Spencer (MVP)]

Pardon me for jumping in.

Yes, the union does start a new query. It then Unions the two separate queries
into one set of data. In other words, it UNIONS the two (or more) sets you have
requested into one set.

BUT, any order by clause is only imposed after the UNION of the separate queries
takes place and applies to the entire combined recordset. And the only order by
that applies is the one in the last SQL Statement - B U T it uses the first SQL
statement's field names. I always get this confused so I've resorted to using
the column number in my sorts in most cases. So to sort by xxx_Sales.Date I
might choose to use Order By 2.


SELECT DISTINCT LOCAL_Sales.SaleID, LOCAL_Sales.Date,
LOCAL_Sales.CardRecordID, LOCAL_Sales.InvoiceStatusID
FROM LOCAL_Sales
UNION
SELECT DISTINCT HISTORY_Sales.SaleID, HISTORY_Sales.Date,
HISTORY_Sales.CardRecordID, HISTORY_Sales.InvoiceStatusID
FROM HISTORY_Sales LEFT JOIN LOCAL_Sales
ON HISTORY_Sales.SaleID = LOCAL_Sales.SaleID
WHERE LOCAL_Sales.SaleID Is Null
ORDER BY 2

 

[david epsom dot com dot au]

UNION returns only every record in the second query, not returned by the
first query.

UNION ALL would return all records from both queries.

Where clause on your second query is surpurfluess - but probably makes query
run faster.

Leave the where clause there, and change UNION to UNION ALL, and it may be
even faster.

(david)

SELECT DISTINCT LOCAL_Sales.SaleID, LOCAL_Sales.Date,

LOCAL_Sales.CardRecordID, LOCAL_Sales.InvoiceStatusID

FROM LOCAL_Sales

UNION SELECT DISTINCT HISTORY_Sales.SaleID, HISTORY_Sales.Date,

HISTORY_Sales.CardRecordID, HISTORY_Sales.InvoiceStatusID

FROM HISTORY_Sales LEFT JOIN LOCAL_Sales ON HISTORY_Sales.SaleID = LOCAL_Sales.SaleID

WHERE LOCAL_Sales.SaleID Is Null;

This returns exactly every reord in "LOCAL_Sales" as well as every record

in "HISTORY_Sales" not duplicated in "LOCAL_Sales". My question is: how is
the SaleID field in the WHERE clause interpreted as applying only to the
JOIN clause and not the SELECT clause?

 

[Guest]

Thanks for all your replies, guys

A great help

Ali