Re: UNION query. Wrong answears.

Scott, What do you get if you run:

SELECT * FROM
(SELECT Clearance.ClearDate as CLDATE, ORDER1.FULFILLMENTCODE AS FCODE
FROM Clearance INNER JOIN
ClearanceOrder ON Clearance.ClearanceID =
ClearanceOrder.ClearanceID INNER JOIN
Order1 ON ClearanceOrder.Order1ID = Order1.OrderID) A
WHERE A.FCODE = 'NEO' AND CLDATE = '05/18/2007'

and then run:

SELECT * FROM
(SELECT Clearance.ClearDate as CLDATE, ORDER2.FULFILLMENTCODE AS FCODE
FROM Clearance INNER JOIN
ClearanceOrder ON Clearance.ClearanceID =
ClearanceOrder.ClearanceID INNER JOIN
Order2 ON ClearanceOrder.Order2ID = Order2.OrderID) A
WHERE A.FCODE = 'NEO' AND CLDATE = '05/18/2007'

?

RLF

"Scott Burke" <ScottBurke@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:827A8790-7B4F-4411-AEDC-A92111DE12CA@xxxxxxxxxxxxxxxx
We are running SQLSERVER 2005. The following query give the wrong
answear.
WHY????
SELECT * FROM
(SELECT Clearance.ClearDate as CLDATE, ORDER1.FULFILLMENTCODE AS FCODE
FROM Clearance INNER JOIN
ClearanceOrder ON Clearance.ClearanceID =
ClearanceOrder.ClearanceID INNER JOIN
Order1 ON ClearanceOrder.Order1ID = Order1.OrderID
UNION
SELECT Clearance.ClearDate as CLDATE, ORDER2.FULFILLMENTCODE AS FCODE
FROM Clearance INNER JOIN
ClearanceOrder ON Clearance.ClearanceID =
ClearanceOrder.ClearanceID INNER JOIN
Order2 ON ClearanceOrder.Order2ID = Order2.OrderID) A
WHERE A.FCODE = 'NEO' AND CLDATE = '05/18/2007'

The answear is ..... one record.
that is the problem because this query give me 624 records!
SELECT Clearance.ClearDate as CLDATE, ORDER1.FULFILLMENTCODE AS FCODE
FROM Clearance INNER JOIN
ClearanceOrder ON Clearance.ClearanceID =
ClearanceOrder.ClearanceID INNER JOIN
Order1 ON ClearanceOrder.Order1ID = Order1.OrderID
where order1.fulfillmentcode = 'NEO' and clearance.cleardate =
'05/18/2007'


Too my knowlage a union query takes the output of two or more quesies and
combins them into a single table. However that is not what is happing!
Why is it ignoring the other 623 records?

Thank you for your time
Scott Burke






.