Alternative for UNION 1

[wysiwygGER01]

Hi,

I'm working on SQL Server 2005.
I've got the below 2 SQL statements joined together with the UNION operator.
When I run both statements separate the results returned are correct.
If I combine them some results from the top query are not shown?

Is there another way to combine my 2 statements or is something wrong in my statement?

SELECT  EBBUDD.FIELD1 AS jobnumber, PMCONTS.[DESC] AS jobdescription,  
              CASE WHEN ebbudd.optional3 LIKE 'TFSC' THEN ((ebbudd.budamt1 + ebbudd.budamt2)  * CASE ebbudd.optional2 WHEN 'AUD' THEN '1' ELSE himsys.dbo.cscrd.rate END) ELSE 0 END AS amount
FROM         PMCONTS INNER JOIN
                      EBBUDD LEFT OUTER JOIN  himsys.dbo.CSCRD ON himsys.dbo.CSCRD.SOURCECUR = EBBUDD.OPTIONAL2 ON EBBUDD.FIELD1 = PMCONTS.FMTCONTNO
WHERE     (EBBUDD.DATE BETWEEN '20080101' AND '20081231') 
                 AND (EBBUDD.BUDAMT1 <> '0') 
                 AND (himsys.dbo.CSCRD.HOMECUR = 'AUD') 
                 AND (himsys.dbo.CSCRD.RATEDATE = EBBUDD.DATE) 
                OR (EBBUDD.DATE BETWEEN '20080101' AND '20081231') 
                 AND (himsys.dbo.CSCRD.HOMECUR = 'AUD') 
                 AND (himsys.dbo.CSCRD.RATEDATE = EBBUDD.DATE) 
                 AND (EBBUDD.BUDAMT2 <> '0') 
                OR (EBBUDD.DATE BETWEEN '20080101' AND '20081231') 
                 AND (EBBUDD.BUDAMT1 <> '0') AND (EBBUDD.OPTIONAL2 = 'AUD') 
                OR (EBBUDD.DATE BETWEEN '20080101' AND '20081231') 
                AND (EBBUDD.BUDAMT2 <> '0') 
                AND (EBBUDD.OPTIONAL2 = 'AUD')
GROUP BY EBBUDD.OPTIONAL3, EBBUDD.FIELD1, EBBUDD.DATE, PMCONTS.[DESC], EBBUDD.BUDAMT1, EBBUDD.BUDAMT2, EBBUDD.OPTIONAL2, 
                      EBBUDD.LINENUM, himsys.dbo.CSCRD.RATE
UNION
SELECT     ORDNUMBER AS ordernumber, BILNAME AS billingname, 0 AS amount
FROM         OEORDH

 

[haroldholt]

Hi, UNION does a select distinct, if you use UNION ALL instead then you will get both results sets back - with duplicates.

 

[wysiwygGER01]

What can I say...CHAMPION!
Works perfect now!

Thank you!