Union statement, temp table or something else to handle mult-queries

[sap1958]

I have to put together two queries that do not have a common value but both instances must be accounted for on one report

select giftid,giftamount,coreid,address
from gifts
left outer join core
on giftid=coreid
left outer join address
on giftid=addrid

select jobid,coreid,address
from jobs
left outer join core
on jobsid=coreid
left outer join address
on jobs=addrid


essentially a person can give a gift and not be an employee. Transversely a person can be an employee and not have given anything.
I thought about creating two temp tables but is is better to list both queries and do a union statement

Any best practices ideas that also takes into account optimization? The reason is the code must be saved and used in a crystal report for output. Also a person can only have one coreid so I also want to select a distinct coreid

 

[gmmastros]

Usually a UNION query will perform better than temp tables. When you use union, you must make sure that each query has the same number of columns and the same data type. You'll need to modify the 2nd query like this...

select giftid,giftamount,coreid,address
from gifts
left outer join core
on giftid=coreid
left outer join address
on giftid=addrid

select jobid,[!]0,[/!]coreid,address
from jobs
left outer join core
on jobsid=coreid
left outer join address
on jobs=addrid

Instead of 0, you could also use NULL. Your choice.

Also, you should understand the difference between UNION and UNION ALL. UNION will filter out duplicates but will run slower. UNION ALL will allow duplicates and run faster. If your data is naturally distinct anyway, you should use UNION ALL.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom