Help with Join

[MaffewW]

Hi, can anyone advise on this query.

I have a calllist table with unique records (i3_rowid primary key)
and a callhistory table with multiple records joined on the i3_rowid field. I need to do a query to retrieve the most recent finishcode for each record. The query below does the trick. The bit I am stuck on is that I want to include records from the call list which dont have a callhistory so the entire calllist is returned. I am currently bodging it with a union and sub query though I'm sure it must be easy in one query

cheers guys


select
l.i3_RowId,
l.phonenumber,
l.Title,
l.Forename,
l.Surname,
l.Address1,
l.Address2,
l.Address3,
l.Address4,
l.Address5,
l.Address6,
l.PostCode,
l.Datacode,
l.RecordID,
l.source,
h.convert(datetime,convert(varchar,CallDate,106)) as h.calldate,
h.CallTime,
h.FinishCode,
h.Agent
from
calllist l inner join callhistory h1
on l.i3_rowid = h1.i3_rowid
where h1.calltime in (select max(h2.calltime) from callhistory h2 where h1.i3_rowid = h2.i3_rowid)

Matt

Brighton, UK

 

[AtomicWedgie]

Hi,

look at outer joins

Regards,

Atomic Wedgie

 

[SQLDenis]

This might do it...
Untested

select
l.i3_RowId,
l.phonenumber,
l.Title,
l.Forename,
l.Surname,
l.Address1,
l.Address2,
l.Address3,
l.Address4,
l.Address5,
l.Address6,
l.PostCode,
l.Datacode,
l.RecordID,
l.source,
convert(datetime,convert(varchar,h.CallDate,106)) as calldate,
Max(h.CallTime) as Calltime,
h.FinishCode,
h.Agent
from
calllist l left outer join callhistory h1
on l.i3_rowid = h1.i3_rowid
group by l.i3_RowId,
l.phonenumber,
l.Title,
l.Forename,
l.Surname,
l.Address1,
l.Address2,
l.Address3,
l.Address4,
l.Address5,
l.Address6,
l.PostCode,
l.Datacode,
l.RecordID,
l.source,
h.calldate,
h.FinishCode,
h.Agent

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[SQLDenis]

Change this
Max(h.CallTime) as Calltime,
To
Max(coalesce(h.CallTime,'1900-01-01') as Calltime,
because it will throw an error

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/