Join on possible empty table

[nybelle]

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!

[cgyrob]

unless I am missing something in your table structure or what you are trying to achieve I think the following is more of what you are looking for.

Select d.pointerField, Case When clm_dateQualifier = 439 Then Case When isdate(CLM_StartDate) = 1 Then CLM_StartDate Else ' ' END END AS ApplicateDate,
                       Case When clm_dateQualifier = 433 Then CASE When isdate(CLM_StartDate) = 1 Then CLM_StartDate Else ' ' END END AS AccidentDate                       
From claimsdetailsdates a
Join TableD as d on d.id = a.pointer
Where d.Pointer = 2645

[nybelle]

Thanks for the reply cyrob.
Your query returns multiple rows; I want everything in 1 row.

[cgyrob]

Then I am missing something about your schema. What exactly is TableD.Pointer? How many rows would you expect to retrieve from the table with each pointer?

Also what is the difference between TableD.Id and TableD.pointer?