Joining on a possible empty table

[belle9]

I have 2 tables: a claim table and a corresponding date table. There can be multiple dates for each claim, with a qualifier type to distinguish them, and I'd like to retrieve them all in one row. Here's a snippet of what I have:

SELECT
d.PointerField
, a.ApplianceDate
b.AccidentDate

FROM
( select case when isdate(CLM_StartDate) = 1 then CLM_StartDate else '' end as ApplianceDate, Pointer from claimdetailsDates where Pointer= 2645 and clm_dateQualifier = '439') as a
cross join
( select case when isdate(CLM_StartDate) = 1 then CLM_StartDate else '' end as AccidentDate, Pointer from claimdetailsDates where Pointer= 2645 and clm_dateQualifier = '433') as b
INNER JOIN
TableD as d
-- on a.pointer = d.Pointer and b.Pointer = d.Pointer
where d.ID = 2645

I know I have a few things wrong..but I can't seem to figure it out!

Thanks!

 

[markros]

See this

http://forums.asp.net/p/1391544/2975969.aspx#2975969

for string concatenation discussion.

 

[bborissov]

 SELECT d.PointerField
        MAX(CASE WHEN clm_dateQualifier = '439')
                      THEN CLM_StartDate
                 ELSE '' END) AS ApplianceDate,
        MAX(CASE WHEN clm_dateQualifier = '433')
                      THEN CLM_StartDate
                 ELSE '' END) AS AccidentDate

FROM CLM_StartDate A
INNER JOIN TableD as d on a.pointer = d.Pointer
where a.Pointer= 2645                      AND
      a.clm_dateQualifier IN ('433','439') AND
      IsDate(a.CLM_StartDate) = 1
GROUP BY d.PointerField

?


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[belle9]

Yup, that's it pretty much. Thanks Boris!