Skip to main content
Code Review

Return to Question

replaced http://stackoverflow.com/ with https://stackoverflow.com/
Source Link

Could someone review an accepted answered accepted answered I gave on Stack Overflow?

Could someone review an accepted answered I gave on Stack Overflow?

Could someone review an accepted answered I gave on Stack Overflow?

deleted 10 characters in body; edited tags; edited title
Source Link
Jamal
  • 35.2k
  • 13
  • 134
  • 238

Working mysql correlated subquery needs review Retrieving the most recent communication from a user

Could someone review an accepted answered I gave on stackoverflowStack Overflow?

usersUsers

id user_name
1 Walker
2 John
3 Kate


 id user_name
 1 Walker 
 2 John 
 3 Kate

messagesMessages


 id senderid body time
 1 1 ignored 1 2010年04月01日 00:00:00.000
 2 1 ignored 2 2010年04月02日 00:00:00.000
 3 3 ignored 3 2010年04月03日 00:00:00.000
 4 1 msg A to john and kate 2010年04月10日 00:00:00.000
 5 3 msg b from kate to walker and john 2010年04月11日 00:00:00.000

id senderid body time
1 1 ignored 1 2010年04月01日 00:00:00.000
2 1 ignored 2 2010年04月02日 00:00:00.000
3 3 ignored 3 2010年04月03日 00:00:00.000
4 1 msg A to john and kate 2010年04月10日 00:00:00.000
5 3 msg b from kate to walker and john 2010年04月11日 00:00:00.000

messages_recipientsmessages_recipients


 id messageid userid
 1 1 2
 2 1 3
 3 2 2
 4 3 1
 5 4 2
 6 4 3
 7 5 1
 8 5 2

id messageid userid
1 1 2
2 1 3
3 2 2
4 3 1
5 4 2
6 4 3
7 5 1
8 5 2

You can see a list of these messages by running the following sql statement:

SELECT 
 u2.user_name AS Sender, 
 u1.user_name AS Receiver, 
 m.body, 
 m.time
FROM 
 messages m
JOIN 
 messages_recipients mr ON m.id = mr.messageid
JOIN 
 users u1 ON mr.userid = u1.id
JOIN 
 users u2 ON m.senderid = u2.id
ORDER BY 
 time DESC

You can see a list of these messages by running the following SQL statement:

SELECT 
 u2.user_name AS Sender, 
 u1.user_name AS Receiver, 
 m.body, 
 m.time
FROM 
 messages m
JOIN 
 messages_recipients mr ON m.id = mr.messageid
JOIN 
 users u1 ON mr.userid = u1.id
JOIN 
 users u2 ON m.senderid = u2.id
ORDER BY 
 time DESC

Now, that we have the test scenario, the part I want reviewed: returningreturning the most recently communicated message between Walker and, John, and Kate. Here is my SQL statement:

BEGIN
 DECLARE @UserId INT = 1
 --A. Main Query
 SELECT
 CASE 
 WHEN mtemp.senderid = 1 --@UserId 
 THEN 
 CONCAT('Message To: ', receivers.user_name)
 ELSE 
 CONCAT('Message From: ' , senders.user_name)
 END AS MessageType, 
 mtemp.body, 
 mtemp.time 
 FROM 
 messages mtemp 
 INNER JOIN users senders ON 
 mtemp.senderid = senders.id 
 INNER JOIN 
 (
 --B. Inner Query determining most recent message (based on time) 
 -- between @UserID and the person @UserID 
 -- Communicated with (either as sender or receiver)
 select userid,max(maxtime) as maxmaxtime from
 (
 --C.1. First part of Union Query Aggregating sent/received messages on passed @UserId
 SELECT 
 m2.body,
 kk.* 
 FROM 
 `messages` m2 INNER JOIN
 (
 SELECT DISTINCT
 userid,
 MAX(m.time) AS MaxTime
 FROM
 messages m INNER JOIN
 messages_recipients mr ON m.id = mr.messageid AND
 m.senderid = 1 --@UserId
 GROUP BY
 mr.userid
 ) kk on m2.time = kk.MaxTime and m2.senderid = 1 --@UserId
 UNION
 --C.2. Second part of Union Query Aggregating sent/received messages on passed @UserId
 SELECT 
 m1.body,
 jj.* 
 FROM 
 `messages` m1 INNER JOIN
 ----C.2a. Inner most query of users users who sent message to userid
 (SELECT DISTINCT
 senderid as userid,
 MAX(m.time) AS MaxTime
 FROM
 messages m INNER JOIN
 messages_recipients mr ON m.id = mr.messageid AND
 mr.userid = 1 --@UserId
 GROUP BY
 m.senderid) jj on m1.time = jj.MaxTime and m1.senderid = jj.userid 
 ) MaximumUserTime
 group by 
 MaximumUserTime.userid 
 ) AggregatedData on mtemp.time = AggregatedData.maxmaxtime 
 INNER JOIN users receivers on AggregatedData.userid = receivers.id 
 ORDER BY `time` DESC
END

BEGIN
 DECLARE @UserId INT = 1
 --A. Main Query
 SELECT
 CASE 
 WHEN mtemp.senderid = 1 --@UserId 
 THEN 
 CONCAT('Message To: ', receivers.user_name)
 ELSE 
 CONCAT('Message From: ' , senders.user_name)
 END AS MessageType, 
 mtemp.body, 
 mtemp.time 
 FROM 
 messages mtemp 
 INNER JOIN users senders ON 
 mtemp.senderid = senders.id 
 INNER JOIN 
 (
 --B. Inner Query determining most recent message (based on time) 
 -- between @UserID and the person @UserID 
 -- Communicated with (either as sender or receiver)
 select userid,max(maxtime) as maxmaxtime from
 (
 --C.1. First part of Union Query Aggregating sent/received messages on passed @UserId
 SELECT 
 m2.body,
 kk.* 
 FROM 
 `messages` m2 INNER JOIN
 (
 SELECT DISTINCT
 userid,
 MAX(m.time) AS MaxTime
 FROM
 messages m INNER JOIN
 messages_recipients mr ON m.id = mr.messageid AND
 m.senderid = 1 --@UserId
 GROUP BY
 mr.userid
 ) kk on m2.time = kk.MaxTime and m2.senderid = 1 --@UserId
 UNION
 --C.2. Second part of Union Query Aggregating sent/received messages on passed @UserId
 SELECT 
 m1.body,
 jj.* 
 FROM 
 `messages` m1 INNER JOIN
 ----C.2a. Inner most query of users users who sent message to userid
 (SELECT DISTINCT
 senderid as userid,
 MAX(m.time) AS MaxTime
 FROM
 messages m INNER JOIN
 messages_recipients mr ON m.id = mr.messageid AND
 mr.userid = 1 --@UserId
 GROUP BY
 m.senderid) jj on m1.time = jj.MaxTime and m1.senderid = jj.userid 
 ) MaximumUserTime
 group by 
 MaximumUserTime.userid 
 ) AggregatedData on mtemp.time = AggregatedData.maxmaxtime 
 INNER JOIN users receivers on AggregatedData.userid = receivers.id 
 ORDER BY `time` DESC
END

To test in phpMyAdminphpMyAdmin, you'll have to remove the comments and the beginBEGIN/end declareENDDECLARE statements as well. I just wanted to post this as if it would look in a procedure.


 MessageType body time
 Message From: Kate msg b from kate to walker and john 2010年04月11日 00:00:00.000
 Message To: John msg A to john and kate 2010年04月10日 00:00:00.000

MessageType body time
Message From: Kate msg b from kate to walker and john 2010年04月11日 00:00:00.000
Message To: John msg A to john and kate 2010年04月10日 00:00:00.000

So, for the mysql pros out there, isIs there a better way to run this query?

Working mysql correlated subquery needs review

Could someone review an accepted answered I gave on stackoverflow?

users


 id user_name
 1 Walker 
 2 John 
 3 Kate

messages


 id senderid body time
 1 1 ignored 1 2010年04月01日 00:00:00.000
 2 1 ignored 2 2010年04月02日 00:00:00.000
 3 3 ignored 3 2010年04月03日 00:00:00.000
 4 1 msg A to john and kate 2010年04月10日 00:00:00.000
 5 3 msg b from kate to walker and john 2010年04月11日 00:00:00.000

messages_recipients


 id messageid userid
 1 1 2
 2 1 3
 3 2 2
 4 3 1
 5 4 2
 6 4 3
 7 5 1
 8 5 2

You can see a list of these messages by running the following sql statement:

SELECT 
 u2.user_name AS Sender, 
 u1.user_name AS Receiver, 
 m.body, 
 m.time
FROM 
 messages m
JOIN 
 messages_recipients mr ON m.id = mr.messageid
JOIN 
 users u1 ON mr.userid = u1.id
JOIN 
 users u2 ON m.senderid = u2.id
ORDER BY 
 time DESC

Now, that we have the test scenario, the part I want reviewed: returning the most recently communicated message between Walker and John and Kate. Here is my SQL statement:

BEGIN
 DECLARE @UserId INT = 1
 --A. Main Query
 SELECT
 CASE 
 WHEN mtemp.senderid = 1 --@UserId 
 THEN 
 CONCAT('Message To: ', receivers.user_name)
 ELSE 
 CONCAT('Message From: ' , senders.user_name)
 END AS MessageType, 
 mtemp.body, 
 mtemp.time 
 FROM 
 messages mtemp 
 INNER JOIN users senders ON 
 mtemp.senderid = senders.id 
 INNER JOIN 
 (
 --B. Inner Query determining most recent message (based on time) 
 -- between @UserID and the person @UserID 
 -- Communicated with (either as sender or receiver)
 select userid,max(maxtime) as maxmaxtime from
 (
 --C.1. First part of Union Query Aggregating sent/received messages on passed @UserId
 SELECT 
 m2.body,
 kk.* 
 FROM 
 `messages` m2 INNER JOIN
 (
 SELECT DISTINCT
 userid,
 MAX(m.time) AS MaxTime
 FROM
 messages m INNER JOIN
 messages_recipients mr ON m.id = mr.messageid AND
 m.senderid = 1 --@UserId
 GROUP BY
 mr.userid
 ) kk on m2.time = kk.MaxTime and m2.senderid = 1 --@UserId
 UNION
 --C.2. Second part of Union Query Aggregating sent/received messages on passed @UserId
 SELECT 
 m1.body,
 jj.* 
 FROM 
 `messages` m1 INNER JOIN
 ----C.2a. Inner most query of users users who sent message to userid
 (SELECT DISTINCT
 senderid as userid,
 MAX(m.time) AS MaxTime
 FROM
 messages m INNER JOIN
 messages_recipients mr ON m.id = mr.messageid AND
 mr.userid = 1 --@UserId
 GROUP BY
 m.senderid) jj on m1.time = jj.MaxTime and m1.senderid = jj.userid 
 ) MaximumUserTime
 group by 
 MaximumUserTime.userid 
 ) AggregatedData on mtemp.time = AggregatedData.maxmaxtime 
 INNER JOIN users receivers on AggregatedData.userid = receivers.id 
 ORDER BY `time` DESC
END

To test in phpMyAdmin, you'll have to remove the comments and the begin/end declare statements as well. I just wanted to post this as if it would look in a procedure.


 MessageType body time
 Message From: Kate msg b from kate to walker and john 2010年04月11日 00:00:00.000
 Message To: John msg A to john and kate 2010年04月10日 00:00:00.000

So, for the mysql pros out there, is there a better way to run this query?

Retrieving the most recent communication from a user

Could someone review an accepted answered I gave on Stack Overflow?

Users

id user_name
1 Walker
2 John
3 Kate

Messages

id senderid body time
1 1 ignored 1 2010年04月01日 00:00:00.000
2 1 ignored 2 2010年04月02日 00:00:00.000
3 3 ignored 3 2010年04月03日 00:00:00.000
4 1 msg A to john and kate 2010年04月10日 00:00:00.000
5 3 msg b from kate to walker and john 2010年04月11日 00:00:00.000

messages_recipients

id messageid userid
1 1 2
2 1 3
3 2 2
4 3 1
5 4 2
6 4 3
7 5 1
8 5 2

You can see a list of these messages by running the following SQL statement:

SELECT 
 u2.user_name AS Sender, 
 u1.user_name AS Receiver, 
 m.body, 
 m.time
FROM 
 messages m
JOIN 
 messages_recipients mr ON m.id = mr.messageid
JOIN 
 users u1 ON mr.userid = u1.id
JOIN 
 users u2 ON m.senderid = u2.id
ORDER BY 
 time DESC

Now that we have the test scenario, the part I want reviewed: returning the most recently communicated message between Walker, John, and Kate.

BEGIN
 DECLARE @UserId INT = 1
 --A. Main Query
 SELECT
 CASE 
 WHEN mtemp.senderid = 1 --@UserId 
 THEN 
 CONCAT('Message To: ', receivers.user_name)
 ELSE 
 CONCAT('Message From: ' , senders.user_name)
 END AS MessageType, 
 mtemp.body, 
 mtemp.time 
 FROM 
 messages mtemp 
 INNER JOIN users senders ON 
 mtemp.senderid = senders.id 
 INNER JOIN 
 (
 --B. Inner Query determining most recent message (based on time) 
 -- between @UserID and the person @UserID 
 -- Communicated with (either as sender or receiver)
 select userid,max(maxtime) as maxmaxtime from
 (
 --C.1. First part of Union Query Aggregating sent/received messages on passed @UserId
 SELECT 
 m2.body,
 kk.* 
 FROM 
 `messages` m2 INNER JOIN
 (
 SELECT DISTINCT
 userid,
 MAX(m.time) AS MaxTime
 FROM
 messages m INNER JOIN
 messages_recipients mr ON m.id = mr.messageid AND
 m.senderid = 1 --@UserId
 GROUP BY
 mr.userid
 ) kk on m2.time = kk.MaxTime and m2.senderid = 1 --@UserId
 UNION
 --C.2. Second part of Union Query Aggregating sent/received messages on passed @UserId
 SELECT 
 m1.body,
 jj.* 
 FROM 
 `messages` m1 INNER JOIN
 ----C.2a. Inner most query of users users who sent message to userid
 (SELECT DISTINCT
 senderid as userid,
 MAX(m.time) AS MaxTime
 FROM
 messages m INNER JOIN
 messages_recipients mr ON m.id = mr.messageid AND
 mr.userid = 1 --@UserId
 GROUP BY
 m.senderid) jj on m1.time = jj.MaxTime and m1.senderid = jj.userid 
 ) MaximumUserTime
 group by 
 MaximumUserTime.userid 
 ) AggregatedData on mtemp.time = AggregatedData.maxmaxtime 
 INNER JOIN users receivers on AggregatedData.userid = receivers.id 
 ORDER BY `time` DESC
END

To test in phpMyAdmin, you'll have to remove the comments and the BEGIN/ENDDECLARE statements as well. I just wanted to post this as if it would look in a procedure.

MessageType body time
Message From: Kate msg b from kate to walker and john 2010年04月11日 00:00:00.000
Message To: John msg A to john and kate 2010年04月10日 00:00:00.000

Is there a better way to run this query?

id user_name
1 Walker 
2 John 
3 Kate 

 id user_name
 1 Walker 
 2 John 
 3 Kate 

id senderid body time
1 1 ignored 1 2010年04月01日 00:00:00.000
2 1 ignored 2 2010年04月02日 00:00:00.000
3 3 ignored 3 2010年04月03日 00:00:00.000
4 1 msg A to john and kate 2010年04月10日 00:00:00.000
5 3 msg b from kate to walker and john 2010年04月11日 00:00:00.000

 id senderid body time
 1 1 ignored 1 2010年04月01日 00:00:00.000
 2 1 ignored 2 2010年04月02日 00:00:00.000
 3 3 ignored 3 2010年04月03日 00:00:00.000
 4 1 msg A to john and kate 2010年04月10日 00:00:00.000
 5 3 msg b from kate to walker and john 2010年04月11日 00:00:00.000

messages_recipientsmessages_recipients

id messageid userid
1 1 2
2 1 3
3 2 2
4 3 1
5 4 2
6 4 3
7 5 1
8 5 2

 id messageid userid
 1 1 2
 2 1 3
 3 2 2
 4 3 1
 5 4 2
 6 4 3
 7 5 1
 8 5 2

MessageType body time
Message From: Kate msg b from kate to walker and john 2010年04月11日 00:00:00.000
Message To: John msg A to john and kate 2010年04月10日 00:00:00.000

 MessageType body time
 Message From: Kate msg b from kate to walker and john 2010年04月11日 00:00:00.000
 Message To: John msg A to john and kate 2010年04月10日 00:00:00.000

id user_name
1 Walker 
2 John 
3 Kate 

id senderid body time
1 1 ignored 1 2010年04月01日 00:00:00.000
2 1 ignored 2 2010年04月02日 00:00:00.000
3 3 ignored 3 2010年04月03日 00:00:00.000
4 1 msg A to john and kate 2010年04月10日 00:00:00.000
5 3 msg b from kate to walker and john 2010年04月11日 00:00:00.000

messages_recipients

id messageid userid
1 1 2
2 1 3
3 2 2
4 3 1
5 4 2
6 4 3
7 5 1
8 5 2

MessageType body time
Message From: Kate msg b from kate to walker and john 2010年04月11日 00:00:00.000
Message To: John msg A to john and kate 2010年04月10日 00:00:00.000


 id user_name
 1 Walker 
 2 John 
 3 Kate 


 id senderid body time
 1 1 ignored 1 2010年04月01日 00:00:00.000
 2 1 ignored 2 2010年04月02日 00:00:00.000
 3 3 ignored 3 2010年04月03日 00:00:00.000
 4 1 msg A to john and kate 2010年04月10日 00:00:00.000
 5 3 msg b from kate to walker and john 2010年04月11日 00:00:00.000

messages_recipients


 id messageid userid
 1 1 2
 2 1 3
 3 2 2
 4 3 1
 5 4 2
 6 4 3
 7 5 1
 8 5 2


 MessageType body time
 Message From: Kate msg b from kate to walker and john 2010年04月11日 00:00:00.000
 Message To: John msg A to john and kate 2010年04月10日 00:00:00.000
Tweeted twitter.com/#!/StackCodeReview/status/60492807537819648
Source Link
ray023
  • 377
  • 1
  • 3
  • 9
Loading
lang-sql

AltStyle によって変換されたページ (->オリジナル) /