SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 Lost Data when Union ALL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 08/10/2012 :  10:28:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 08/10/2012 :  10:43:17  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/10/2012 :  10:48:02  Show Profile  Reply with Quote
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/

Go to Top of Page

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 08/10/2012 :  10:57:01  Show Profile  Reply with Quote
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?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/10/2012 :  11:07:48  Show Profile  Reply with Quote
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/

Go to Top of Page

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 08/10/2012 :  11:10:21  Show Profile  Reply with Quote
yes, it is.
I don't have a permisson to write a procedure, therefore that is my query likt it.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/10/2012 :  11:11:43  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/10/2012 :  11:12:44  Show Profile  Reply with Quote
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/

Go to Top of Page

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 08/10/2012 :  11:19:07  Show Profile  Reply with Quote
I ran the query, it still doesn't have Tom name on the output.
:(
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/10/2012 :  11:23:47  Show Profile  Reply with Quote
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/

Go to Top of Page

JadeV
Yak Posting Veteran

Canada
62 Posts

Posted - 08/10/2012 :  15:55:47  Show Profile  Reply with Quote
yes, I checked and got it. Thank you very much
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/10/2012 :  16:11:16  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.33 seconds. Powered By: Snitz Forums 2000