SQL Join two tables with a twist

[mattpont]

Hello All,

I have two tables:

tbl_clients:
- clients_id (PK)
- clients_name
- comments_id (FK)


tbl_comments:
- comments_id (PK)
- comments_text
- comments_date


What I need to do is join these two tables together, but here's the twist...I only want the MOST RECENT comment in the table.

i.e.
- I want all the clients in the end table regardless of whether they have comments or not.
- I only want one record per client.
- If there is mulitple comments for a client, I only want the most recent in the results table.

Is this possible?! It's doing my head in.

Thanks a lot

 

[bborissov]

And how you know which comment is most recent? Did you have any DateTime field to show you when the comment is added?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[manmaria]

That means Comments_Id is not unique in the tbl_comments table, is that right? or comments_id and comments_Date combinates make the unique row?

 

[SQLSister]

Your data structure is odd. Right now you can only have one comment Id per client based on the way this is sturcture unless you have clients inthere multiple times with differnt client_ids which would be a very bad thing. Normally the client table would be the primary table and the comments would have the client_id. In this case you can;t have more than one comment per climnet anyway so why are you concerned about the date? Maybe I'm misunderstanding. could you give exmplaes of smalpe data in both tables and the end result you want to see?

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[mattpont]

Hi guys,

The comment which is most recent is governed by the comments_date field.

comments_id is the primary key for tbl_comments.

Each client can have many comments.

I just need to create a view where I can see a table with all the clients with their most recent comment next to them (all other comments will be ignored).

Sorry! I just realised I messed up my original diagrams (I had draw simplified versions, but ballsed it up nevertheless).

It should be:
tbl_clients:
- clients_id (PK)
- clients_name


tbl_comments:
- comments_id (PK)
- clients_id (FK)
- comments_text
- comments_date


Thanks!

 

[davejam]

its a bit backwards as was being said above

ideally (in my world) you'd have

tbl_clients:
- clients_id (PK)
- clients_name

tbl_comments:
- comments_id (PK)
- clients_id (FK)
- comments_text
- comments_date

then you would have a fairly simple select

SELECT a.clients_id, a.clients_name, b.comments_text, b.comments_date 
FROM tbl_clients A INNER JOIN tbl_comments B 
ON a.clients_id = b.clients_id 
WHERE b.clients_id = 
 SELECT TOP 1 clients_id 
 FROM tbl_comments 
 WHERE tbl_comments.clients_id = a.client_id 
 ORDER BY tbl_comments.comments_date Desc

something along those lines anyway...

if that what you're looking for anyway

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[davejam]

oops

messed up a little there i thing

SELECT a.clients_id, a.clients_name, b.comments_text, b.comments_date 
FROM tbl_clients A INNER JOIN tbl_comments B 
ON a.clients_id = b.clients_id 
WHERE a.clients_id = 
 SELECT TOP 1 clients_id 
 FROM tbl_comments 
 WHERE tbl_comments.clients_id = a.clients_id 
 ORDER BY tbl_comments.comments_date Desc

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[SQLSister]

Ok that makes more sense.


Use a derived table and join to it.

select c.clients_name, cm.commments_text, cm.comments_date from tblclients c join
join tblComments cm on c.client_id = cm.client_id 
join 
(select client_id, max(comments_date)  and cmdate from tblecomments group by client_id) a 
on cm.client_id = a.client_id and cm.comments_date= a.cmdate

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[davejam]

did it again, unfortunately theres no way to edit my posts!!

SELECT a.clients_id, a.clients_name, b.comments_text, b.comments_date 
FROM tbl_clients A INNER JOIN tbl_comments B 
ON a.clients_id = b.clients_id 
WHERE b.comments_id = 
 SELECT TOP 1 tbl_comments.comments_id 
 FROM tbl_comments 
 WHERE tbl_comments.clients_id = a.clients_id 
 ORDER BY tbl_comments.comments_date Desc

daveJam

*two wrongs don't make a right..... but three lefts do!!!!*

 

[mattpont]

Wicked.

That seems that have done the trick, thankyou everyone for your very quick and accurate responses.

Cheers.