Lost Data when Union ALL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Lost Data when Union ALL

Author

Topic

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-08-10 : 10:28:38

I don't know why I lost data when I did UNION ALL 2 fields.
ARe there any ways to do different than using UNION ALL?

My query is
select a.taskID, a.assignee,a.time,b.area
from task a, location b
where a.taskID = b.locationID

UNION ALL

select a.taskID, a.assignee, a.time, b.area
from task a, location b
where a.assignee = b.assignee

group by a.assignee

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-10 : 10:43:17

that shoould give an error as the second subquery has a group by clause and non-aggregate other columns in the select.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 10:48:02

quote:
Originally posted by JadeV

I don't know why I lost data when I did UNION ALL 2 fields.
ARe there any ways to do different than using UNION ALL?

My query is
select a.taskID, a.assignee,a.time,b.area
from task a, location b
where a.taskID = b.locationID

UNION ALL

select a.taskID, a.assignee, a.time, b.area
from task a, location b
where a.assignee = b.assignee

group by a.assignee

post full query please
i'm sure the above query is not full one as it doesnt even compile
also if you can show data sample and example what data you lost that will also help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-08-10 : 10:57:01

MY full query is

select a.RecID, a.CreatedDateTime, a.AssignedTo, a.ParentPublicID,
b.IncidentGroup, b.ResAreaOfFailure,b.ResAreaOfFailureDetail,b.ResAreaOfFailureItem --9
from task a, timeRecord b
where a.recID = b.TaskID
--and a.SubDepartment = b.IncidentGroup
and a.parentPublicID = b.parentPublicID
and b.incidentgroup not in ('Corporate Systems','Network Operations', 'Maintenance Base')
and a.createdDateTime between '2012-07-01 00:00:00.000' and '2012-07-31 00:00:00.000'
group by a.RecID, a.CreatedDateTime, a.AssignedTo, a.ParentPublicID,
b.IncidentGroup, b.ResAreaOfFailure,b.ResAreaOfFailureDetail,b.ResAreaOfFailureItem
order by a.assignedTo,a.ParentPublicID

UNION ALL

select c.RecID, a.CreatedDateTime, a.AssignedTo, a.ParentPublicID,
b.IncidentGroup, b.ResAreaOfFailure,b.ResAreaOfFailureDetail,b.ResAreaOfFailureItem --10
from task a, serviceDesk c, timerecord b
where a.recID = b.TaskID
and a.parentPublicID = c.serviceDeskID
and b.incidentgroup not in ('Corporate Systems','Network Operations', 'Maintenance Base')
and a.createdDateTime between '2012-07-01 00:00:00.000' and '2012-07-31 00:00:00.000'
group by c.RecID, a.CreatedDateTime, a.AssignedTo, a.ParentPublicID,
b.IncidentGroup, b.ResAreaOfFailure,b.ResAreaOfFailureDetail,b.ResAreaOfFailureItem
order by AssignedTo

====================
Because in the table timerecord doesn't have any records of the name Tom, but in the task table has.
I won't to know that is a reason to lost the data?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 11:07:48

this wont compile either due to order by in between. Are you really posting the actually used query ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-08-10 : 11:10:21

yes, it is.
I don't have a permisson to write a procedure, therefore that is my query likt it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 11:11:43

and now the answer for your question
Because in the table timerecord doesn't have any records of the name Tom, but in the task table has.
I won't to know that is a reason to lost the data?

the reason is you're doing inner join so it will return only data having matching details in timerecord. if you want results regardless of match use LEFT JOIN instead


...
select c.RecID, a.CreatedDateTime, a.AssignedTo, a.ParentPublicID, 
b.IncidentGroup, b.ResAreaOfFailure,b.ResAreaOfFailureDetail,b.ResAreaOfFailureItem --10
from task a
INNER JOIN  serviceDesk c
ON a.parentPublicID = c.serviceDeskID 
LEFT JOIN timerecord b
ON a.recID = b.TaskID
and 
and b.incidentgroup not in ('Corporate Systems','Network Operations', 'Maintenance Base')
WHERE a.createdDateTime between '2012-07-01 00:00:00.000' and '2012-07-31 00:00:00.000'
group by c.RecID, a.CreatedDateTime, a.AssignedTo, a.ParentPublicID,
b.IncidentGroup, b.ResAreaOfFailure,b.ResAreaOfFailureDetail,b.ResAreaOfFailureItem
order by AssignedTo

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 11:12:44

quote:
Originally posted by JadeV

yes, it is.
I don't have a permisson to write a procedure, therefore that is my query likt it.

but its not even having proper syntax. so how are you going to run it? remove the unwanted order by before UNION ALL and it will work

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-08-10 : 11:19:07

I ran the query, it still doesn't have Tom name on the output.
:(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 11:23:47

quote:
Originally posted by JadeV

I ran the query, it still doesn't have Tom name on the output.
:(

check if Toms record staisfies other conditions (created date in range chosen), matching record in servicedesk table etc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JadeV
Yak Posting Veteran

62 Posts

Posted - 2012-08-10 : 15:55:47

yes, I checked and got it. Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 16:11:16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources