UNION query. Wrong answears. -- sql server mseq

Scott Burke
5/17/2007 8:37:01 AM

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

Scott Burke
5/17/2007 2:28:05 PM

Hi Russell, Thanks for the responce. The problem was found! "Union" will
remove duplicate records. "Union all" will NOT.

Thanks for your time.
Scott Burke

[quoted text, click to view]

Russell Fields
5/17/2007 3:57:36 PM

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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server mseq

sql server mseq : UNION query. Wrong answears.