| Author |
 Topic  |
|
|
JadeV
Yak Posting Veteran
Canada
62 Posts |
 Posted - 08/10/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 08/10/2012 : 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
India
48076 Posts |
Posted - 08/10/2012 : 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
Canada
62 Posts |
Posted - 08/10/2012 : 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
India
48076 Posts |
Posted - 08/10/2012 : 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
Canada
62 Posts |
Posted - 08/10/2012 : 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
India
48076 Posts |
Posted - 08/10/2012 : 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
India
48076 Posts |
Posted - 08/10/2012 : 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
Canada
62 Posts |
Posted - 08/10/2012 : 11:19:07
|
I ran the query, it still doesn't have Tom name on the output.
:(
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 08/10/2012 : 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
Canada
62 Posts |
Posted - 08/10/2012 : 15:55:47
|
| yes, I checked and got it. Thank you very much |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 08/10/2012 : 16:11:16
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Lost Data when Union ALL
