help with joins

Hi all,

I have two tables which look like the following

Table A

ID Name

1 ABC

2 XYZ

3 PQR

Table B

Comments DateTime

ABC comments hi 10/10/06

NewComments ABC are 1/1/07

XYZ my comments 12/12/06

I want the output of the above to look like this: Left join on table A and recent most date

Name Date

ABC 1/1/07

XYZ 12/12/06

PQR

There is no column in common for these tables to join. So i am running a cursor through all names in table A

cursor for select name from table A

I will find each name in comments section and get recent most date with order by date desc. But this doesnt give the row 'PQR'.

Is there a way to left join these two tables.

Thanks

Maybe you want something like this?

create table #table_a
(
id int,
name varchar(10)
)

insert into #table_a values (1, 'ABC')
insert into #table_a values (1, 'XYZ')
insert into #table_a values (1, 'PQR')

create table #table_b
(
comments varchar(100),
timestamp datetime
)

insert into #table_b values ('ABC comments hi', '10/10/06')
insert into #table_b values ('NewComments ABC are', '01/01/2007')
insert into #table_b values ('XYZ my comments', '12/12/2006')

select
a.name,
max(b.timestamp)
from #table_a a
left outer join #table_b b
on b.comments like ('%' + a.name + '%')
group by
a.name
order by 1
;

|||

Hi Dan,

Thanks for the reply.

The Query works.But I have 5 more columns from table B to be added in the select list.

Even when add the same columns in the group by list,The Query return too many incorrect rows.

Any suggestion what i should do

Thanks

|||

I am not sure what you are trying to do with these additional columns.

Are they supposed to be from the record in Table_B that has the latest timestamp?

If so, we just need to use the existing query as a subquery. Like this:

select
tmp.*,
b.stuff1,
b.stuff2,
b.stuff3,
b.stuff4
from
(
select
a.name,
max(b.timestamp) max_timestamp
from #table_a a
left outer join #table_b b
on b.comments like ('%' + a.name + '%')
group by
a.name
) tmp
left outer join #table_b b
on b.timestamp = tmp.max_timestamp and
b.comments like ('%' + tmp.name + '%')
order by 1
;

|||

Thanks a lot Dan.Works perfectly fine for me.

Thanks