Avatar of HLRosenberger
HLRosenberger
Flag 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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
ralmada

8/22/2022 - Mon
gplana

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

Ess Kay

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)
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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
HLRosenberger

ASKER
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.
Ess Kay

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
ralmada

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ess Kay

ps,

unique is in oracle, mssql server uses destict

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

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
ralmada

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ralmada

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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck