help with union

HLRosenberger
HLRosenberger used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Commented:
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)

Author

Commented:
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
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.

Commented:
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

Commented:
ps,

unique is in oracle, mssql server uses destict

http://psoug.org/snippet/SELECT-Get-DISTINCT-UNIQUE-values_667.htm

Commented:
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)
Please check my comment, there was a typo in there:

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial