Not getting distinct results with inner join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Not getting distinct results with inner join

Author

Topic

yoder
Starting Member

6 Posts

Posted - 2007-03-05 : 19:17:26

Hello, first post since I can usually find answers by reading the forums. I've searched the internet up and down and for some reason I can't get this query to work properly.

I have two tables:

ticket

ticket_id
ticket_to
ticket_from

message

message_id
ticket_id
message

There can be several messages per ticket, connected by the ticket_id. I'd like to only pull only the most recent message, as well as the results from the ticket table. Currently I'm getting all messages per ticket.

Here's what I have:

SELECT distinct ticket.ticket_to, ticket.ticket_from, message.ticket_id, message.message
FROM tickets
INNER JOIN message ON tickets.ticket_id = message.ticket_id
GROUP BY message.ticket_id, message, ticket_to, ticket_from

Any help would be greatly appreciated! Thanks much.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-05 : 19:23:40

How do you know which message is the most recent one? Do you have a datetime column that tells you this? Or is it based upon the highest message_id?

Tara Kizer

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-03-05 : 19:27:48

This may work if it's based upon the highest message_id:


SELECT z.ticket_id, ti.ticket_to, ti.ticket_from, me.message
FROM ticket ti
INNER JOIN message me
ON ti.ticket_id = me.ticket_id
INNER JOIN
(
	SELECT t.ticket_id, MAX(m.message_id) AS MaxMessId
	FROM tickets t
	INNER JOIN message m
	ON t.ticket_id = m.ticket_id 
	GROUP BY t.ticket_id
) z
ON me.ticket_id = z.ticket_id AND me.message_id = z.MaxMessId

Tara Kizer

yoder
Starting Member

6 Posts

Posted - 2007-03-05 : 19:52:49

Works perfectly! Can't thank you enough, I've been hammering on this one all day.

Yes, it's based on the highest message_id. There's a timestamp in there as well, but I dumbed down the code for the forum.

Thanks again!

Erik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources