Creating join on 2 tables both if 2nd table has entry or not

[russellobrien]

Help!

I have 2 tables, the first a "customer" with relevant details. I have a second table listing customer "calls". Some will have called, some not.
I need to join the tabel such that I create a column on the end of the customer details that says whether there was a call or not (and also it picks the earliest call date)

i.e:

CUSTOMER

NAME DETAILS
jon seattle
bob london
bill berlin

etc

CALLS

NAME DATE
jon 1/1/2004
jon 3/1/2004
bob 22/11/2003

which returns

CALLS_FROM_CUSTOMERS

NAME DETAILS CALL(y/n) DATE(if y)
jon seatlle y 1/1/4
bob london y 22/11/3
bill berlin n NULL


Any help much appreciated.

Russell

 

[nicsin]

Hi Russel,

try this:

select customer.name, customer.details, calls.date 
from customer left join calls on customer.name = calls.name

 

[DanChard]

Or this:

select name, details, temp1 as [call (y/n)], min(date) as [date (if y)]
from (select customer.name, customer.details, iif(isnull(calls.date),'n','y') as temp1, calls.date
from customer
left join calls
on customer.name = calls.name)
group by name, details, temp1
order by 4 desc;