Get all records

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