Hi I have a table route with
routeid name
1 a
2 b
3 c
4 d
5 e
..
10 j
exExposure
caseid routeid
200 1
300 2
..
Now I have written a query which will join some other tables to get the results
select
er.exRouteID as RouteID,er.[Name] as route,
'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),
'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),
from cacase c
left join exExposure e on c.caCaseID=e.caCaseID
Left Join Route er on er.RouteID=e.RouteID
where --conditions
When i run this query I am getting only the routes which have the values exist in exExposure table.If for some routes like i, j I don't have corresponding casesids.But i need to get all the routes which exist in Route table even they don't have caseids.
For this i am trying like this:
select
er.exRouteID as RouteID,er.[Name] as route,
'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),
'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),
from cacase c
left join exExposure e on c.caCaseID=e.caCaseID
Left Join Route er on er.RouteID=e.RouteID
where --conditions
Union
Select er.RouteID,er.Name,0,0
From Route r
where r.routeID Not IN (Selelct RouteID From above Selelct query with the same where condtions and joins)
Any other better way for getting the unmatched routes other than this.
Thanks in advance.
I'm not sure what your question is, but I'll take a guess.
If you want the original query to contain all Routes (Route table), then name the Route table as the FIRST table in the series of joins, OR, in the current query, use a RIGHT JOIN with Routes. Either option 'should' give you what you seek.
|||You can get your desired result using the following query..
select
er.exRouteID as RouteID,er.[Name] as route,
'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),
'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),
from cacase c
Left Join exExposure e on c.caCaseID=e.caCaseID And {all Your exExposure table based conditions}
Left Join Route er on er.RouteID=e.RouteID And {all your Route table based conditions}
where {other conditions}
|||Hi,
I placed all the ExExposure table condion after the exExposure table join this is for Route table also. But now also it is giving Routes which have the CaseID s only. Any other way.
Thanks in advance.
|||The following query will be help you...
Code Snippet
select
er.exRouteID as RouteID,er.[Name] as route,
'<=5 Years'=SUM(CASE WHEN((pp.paAgeUnitId=1) THEN 1 ELSE 0 END ),
'6-12 Years'=SUM(CASE WHEN((pp.paAgeUnitId=2) THEN 1 ELSE 0 END ),
from Route er
Left Join exExposure e on er.RouteID=e.RouteID
Left Join cacase c on c.caCaseID=e.caCaseID
where other condition
