Link to home
Start Free TrialLog in
Avatar of HLRosenberger
HLRosenbergerFlag for United States of America

asked on

help with union

I have attached the result of a UNION.  Where there are duplicate categories, I do not want the NULL hour/date record.  When the NULL hour/date record is the only one for a category, then I want to keep it.

 When the How can I do this?  

here's the SQL

SELECT Category , annualhours AS Hours,  Date AS Date  

FROM training_details_report
where EmpUID = 2368 and annualhours > 0 and
(date BETWEEN '1/1/2011' AND '12/31/2011')

union

select Category, null as hours, null as date from BCS_Training_Categories
union.png
Avatar of gplana
gplana
Flag of Spain image

Try this:

SELECT Category, annualhours AS Hours,  Date AS Date
FROM training_details_report tdr LEFT JOIN (
(SELECT Category
FROM training_details_report
where EmpUID = 2368 and annualhours > 0 and
(date BETWEEN '1/1/2011' AND '12/31/2011')
UNION
SELECT Category
FROM BCS_Training_Categories 
)
) c ON c.Category = tdr.Category
where EmpUID = 2368 and annualhours > 0 and
(date BETWEEN '1/1/2011' AND '12/31/2011')

Open in new window

you can use unique


select unique * from (SELECT Category , annualhours AS Hours,  Date AS Date  

FROM training_details_report
where EmpUID = 2368 and annualhours > 0 and
(date BETWEEN '1/1/2011' AND '12/31/2011')

union

select Category, null as hours, null as date from BCS_Training_Categories)
Avatar of HLRosenberger

ASKER

gplana: I tried yours and it does not seem to work.

esskayb2d: I tried yours and it does not like the unique keyword.  This is Microsoft SQL server
I've requested that this question be deleted for the following reason:

the supplied answers did not work.   I took another approach, doing a UNION,  category IN() for one set, and category NOT IN() for teh other set.
sorry here is the actual syntax, that was just off the top of my head





the below query by itself will only provide distinct values

select col from table1
union
select col from table2
if you did want duplicates you would have to do

select col from table1
union all
select col from table2
May I suggest you try this?

select a.Category, b.annualhours as Hours, b.Date as Date
from BCS_Training_Categories a
left join (
	select * from training_details_report b
	where EmpUID = 2368 and annualhours > 0 and
	(date BETWEEN '1/1/2011' AND '12/31/2011')
) b on a.Category = b.Category

Open in new window

ps,

unique is in oracle, mssql server uses destict

http://psoug.org/snippet/SELECT-Get-DISTINCT-UNIQUE-values_667.htm
so, in sum, just run the query you have and a query over it using distinct values


select distinct * from
(SELECT Category , annualhours AS Hours,  Date AS Date  
FROM training_details_report
where EmpUID = 2368 and annualhours > 0 and
(date BETWEEN '1/1/2011' AND '12/31/2011')
union
select Category, null as hours, null as date from BCS_Training_Categories)
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry HLRosenberg, I guess objecting this, is the only way to get your attention. Please have look at my last comment http:#a37998302 . I think this will get what you want without any  complicated UNION and IN, NOT IN as you currently have.