left join Distinct alternative

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

SQL Server 2008 Forums

Transact-SQL (2008)

left join Distinct alternative

Author

Topic

LittlePaint
Starting Member

2 Posts

Posted - 2012-09-06 : 14:29:01

I have the following query:

SELECT X.MigrationID, MachineName, X.MessagePostDateEST, Y.CommentText FROM TABLE1 AS X
LEFT JOIN TABLE2 AS Y ON X.MigrationID=Y.MigrationID
WHERE X.MessagePostDateEST = (SELECT MAX(CAST(MessagePostDateEST AS datetime)) FROM TABLE1 WHERE MigrationID=X.MigrationID)
ORDER BY CAST(X.MessagePostDateEST AS datetime) DESC

Which produces results like this:
MigrationID MachineName MessagePostDateEST CommentText
{103310B73F8A} LM74 09/06/2012 11:46:24.903 AM NULL
{40050427F350} LV82 09/06/2012 11:30:53.967 AM Comment 1
{40050427F350} LV82 09/06/2012 11:30:53.967 AM Comment 2
{40050427F350} LV82 09/06/2012 11:30:53.967 AM Comment 3
{72DF3E618FE0} LM60 09/06/2012 10:29:07.020 AM NULL

What I need is a way to modify the query so that it will only return one record that has multiple comments associated with it. For example, I want results that look like this:
MigrationID MachineName MessagePostDateEST CommentText
{103310B73F8A} LM74 09/06/2012 11:46:24.903 AM NULL
{40050427F350} LV82 09/06/2012 11:30:53.967 AM Comment 1
{72DF3E618FE0} LM60 09/06/2012 10:29:07.020 AM NULL

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-06 : 15:00:04

Try this...I am randomly selecting the MAX() comment since you gave no requirement for how to decide which one to pick.

SELECT X.MigrationID, MachineName, X.MessagePostDateEST, Y.CommentText FROM TABLE1 AS X
LEFT JOIN (SELCT MigrationID, MAX(CommentText) FROM TABLE2 GROUP BY MigrationID) Y ON X.MigrationID=Y.MigrationID
WHERE X.MessagePostDateEST = (SELECT MAX(CAST(MessagePostDateEST AS datetime)) FROM TABLE1 WHERE MigrationID=X.MigrationID)
ORDER BY CAST(X.MessagePostDateEST AS datetime) DESC

-Chad

LittlePaint
Starting Member

2 Posts

Posted - 2012-09-13 : 11:08:17

Chad, many thanks, that is exactly what I was looking for. I only had to add a column alias from what you posted in order to have it work:

SELECT X.MigrationID, MachineName, X.MessagePostDateEST, Y.MCT FROM TABLE1 AS X
LEFT JOIN (SELECT MigrationID, MAX(CommentText) AS MCT FROM TABLE2 GROUP BY MigrationID) AS Y ON X.MigrationID=Y.MigrationID
WHERE X.MessagePostDateEST = (SELECT MAX(CAST(MessagePostDateEST AS datetime)) FROM TABLE1 WHERE MigrationID=X.MigrationID)
ORDER BY CAST(X.MessagePostDateEST AS datetime) DESC

chadmat
The Chadinator

1974 Posts

Posted - 2012-09-13 : 12:35:12

You are welcome

-Chad

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources