RIGHT JOIN

[jonniejoejonson]

$sql="SELECT MAX(mr.messageId) AS maxMessageId, mr.threadId 
FROM messages_recipients AS mr
RIGHT JOIN thread_recipients AS tr ON tr.threadId=mr.threadId
WHERE mr.recipientUserId='2'
GROUP BY mr.threadId";

 

If the above SELECT should find a row in message_recipients with a threadId of 1

and thread_recipients has multiple rows with threadId=1..

then because I have a RIGHT JOIN,

I would expect it to return as many rows as the number of rows in thread_recipients where threadId=1

 

It is however only returning one row regardless...

Can you tell me why it is doing this...

Kind regards J

[requinix]

You're using a GROUP BY. You will only get one row per value per field grouped-by.

[jonniejoejonson]

Thanks requinix for your response...

 

So do you know how I would achieve what I am trying to do, whereby if the 'thread_recipients' table has multiple rows on ( tr.threadId=mr.threadId) then it selects all of them... as I have to GROUP the 'message_recipients' table by threadId....

 

Your continued support is much appreciated.

Kind regards J

[jonniejoejonson]

To help I have tried to explain better what I am asking...

 

table messages

messageId (INT)

threadId (INT)

recipientUserId (INT)

message (TEXT)

 

table thread_recipients

recipientUserId (INT)

threadId (INT)

 

 

I want to SELECT a specific users most recent message for each thread from the message table.

I also want to get all the other users that are part of the selected threads from the thread_recipients table.

 

 

I thought the following would work...

 

$sql="SELECT MAX(m.messageId) AS maxMessageId, m.threadId, m.message 
FROM messages AS m
RIGHT JOIN thread_recipients AS tr ON tr.threadId=m.threadId
WHERE m.recipientUserId='2'
GROUP BY m.threadId";

 

 

but due to the GROUP BY, it only selects one row for each threadId, regardsless to the number of users that are part of that thread.

Perhaps it is not possible to do in one query?...

Kind regards to any responders.

[fenway]

First, the use of RIGHT JOIN is killing me -- switch the LEFT JOIN like the rest of us -- of course, you're never actually using the non-matching aspect, so INNER JOIN is the better choice here.

 

Second, getting the most recent for each thread is not achieved with group by -- you need to self-join and find the records that have no messages with more recent dates.  I've posted this kind of solution at least twice in the last week.

 

 

[jonniejoejonson]

thanks for your response...

I thought that because I am selecting the max messageId of a GROUP BY threadId, I was therefore also selecting the most recent message for that threadId, because the most recent message, will have the highest messageId.

Would this not be a more efficient way to do this rather than with a self Join and comparing the dates?

I will try and scout out some of your previous posts on this...  Regards J

[fenway]

In general, when you use GROUP BY, you can't rely on the values on any non-aggregated column.

[jonniejoejonson]

Thanks Fenway for the response...

 

Thanks to R397 (devshed forum) for the following...

SELECT x.maxMessageId
     , m.threadId
     , m.message 
     , tr.recipientUserId
  FROM ( SELECT threadId
              , MAX(messageId) AS maxMessageId      
           FROM messages 
          WHERE recipientUserId = 2
         GROUP 
             BY threadId ) AS x
INNER
  JOIN messages AS m
    ON m.messageId = x.maxMessageId
INNER
  JOIN thread_recipients AS tr 
    ON tr.threadId = m.threadId

[fenway]

First, I really dislike when topics are posted on different web-site forums -- seems like a waste of everyone's time.

 

Second, that solution will not work if you ever wanted to test for 2 different userIDs.

[jonniejoejonson]

Thanks for your response... Sorry you feel that your time is being wasted, but i'm not sure what difference it makes if i post in multiple forums?...

i.e. you and 10 other people may be kindly helping to work on this post, whether you read it in this forum or another it makes no difference...

I am always appreciative of peoples help, and as long as I follow through to post the correct answer in all forums that i post, i think that everyone benefits.

 

With regard to the solution... Can you explain further as to why this would not work because i'm not sure what you mean.

Regards J.

[jonniejoejonson]

Sorry Fenway, my previous post came across as a little snotty and without people like yourself taking time to help, these forums wouldn't work... It is much appreciated. J

[fenway]

Personally, I find it frustrating to work towards a solution, only to find out that the OP has gone elsewhere (i.e. devshed) as well.

 

Why should I bother helping you if you're just going to get someone else to as well? I might as well wait until they give up.

 

In terms of the solution, you'll notice that userID isn't one of the columns returned from that subquery.