A PM is a private message.
Here is I want to do.
I want to know the UUID, subject, and also if there are any unread messages. I just want to know this for one specific user and that specific users specific folder.
The conversation_uuid is the same in both pm and pm2user tables for one specific conversation, it can exist several times in both tables but only one time per user in pm2user. So which one I group by shouldn't make any difference.
Let me show you some examples..
This is the result WITHOUT any group by.
unread conversation_uuid subject datetime
0 02f48850-4c4f-11de-a8c8-ee89e0c74506 Problemet Löst 2009-03-13 21:08:45
0 117753b2-4c4e-11de-a8c8-ee89e0c74506 50 tusenkr 2009-02-06 11:35:28
0 453e77ed-4c4f-11de-a8c8-ee89e0c74506 videoklipp 2009-02-03 12:11:07
0 453e77ed-4c4f-11de-a8c8-ee89e0c74506 videoklipp 2009-02-03 12:09:15
0 453e77ed-4c4f-11de-a8c8-ee89e0c74506 videoklipp 2009-02-03 11:59:01
0 6cb7839b-4c4e-11de-a8c8-ee89e0c74506 Heej 2009-01-30 15:58:37
0 fd229e63-4c4e-11de-a8c8-ee89e0c74506 Nya videos 2009-01-27 10:37:23
0 fd229e63-4c4e-11de-a8c8-ee89e0c74506 Nya videos 2009-01-22 12:24:01
0 e88c5ee7-4c4e-11de-a8c8-ee89e0c74506 lösenord 2009-01-13 16:54:32
0 e88c5ee7-4c4e-11de-a8c8-ee89e0c74506 lösenord 2009-01-13 14:25:31
0 e88c5ee7-4c4e-11de-a8c8-ee89e0c74506 lösenord 2009-01-12 21:11:01
0 e88c5ee7-4c4e-11de-a8c8-ee89e0c74506 lösenord 2009-01-11 23:02:24
0 e88c5ee7-4c4e-11de-a8c8-ee89e0c74506 lösenord 2009-01-11 22:27:32
0 319e27a5-4c4f-11de-a8c8-ee89e0c74506 Tjena gubben;-) 2008-11-27 00:36:19
0 8e184b0b-4c4e-11de-a8c8-ee89e0c74506 hej hej 2008-11-24 13:19:52
And here is the same query WITH group by
unread conversation_uuid subject datetime
0 02f48850-4c4f-11de-a8c8-ee89e0c74506 Problemet Löst 2009-03-13 21:08:45
0 117753b2-4c4e-11de-a8c8-ee89e0c74506 50 tusenkr 2009-02-06 11:35:28
0 453e77ed-4c4f-11de-a8c8-ee89e0c74506 videoklipp 2009-02-03 11:59:01
0 6cb7839b-4c4e-11de-a8c8-ee89e0c74506 Heej 2009-01-30 15:58:37
0 fd229e63-4c4e-11de-a8c8-ee89e0c74506 Nya videos 2009-01-22 12:24:01
0 e88c5ee7-4c4e-11de-a8c8-ee89e0c74506 lösenord 2009-01-11 22:27:32
0 319e27a5-4c4f-11de-a8c8-ee89e0c74506 Tjena gubben;-) 2008-11-27 00:36:19
0 e72eea66-4c4d-11de-a8c8-ee89e0c74506 Ålder 2008-11-19 08:36:07
0 3cc9a369-4c4f-11de-a8c8-ee89e0c74506 Tjoppi 2008-11-15 14:38:40
0 e16dc545-4c4e-11de-a8c8-ee89e0c74506 Korrekt ålder 2008-11-07 20:07:27
0 04c3458f-4c4f-11de-a8c8-ee89e0c74506 Read Reciept 2008-11-03 21:48:19
0 8e184b0b-4c4e-11de-a8c8-ee89e0c74506 hej hej 2008-11-03 21:38:37
0 c4a11330-4c4e-11de-a8c8-ee89e0c74506 Helsinki 2008-11-02 22:55:12
0 28fda094-4c4f-11de-a8c8-ee89e0c74506 Tja ba! 2008-10-30 11:10:27
0 98166dd3-4c4e-11de-a8c8-ee89e0c74506 Hej! 2008-10-28 16:01:55
SELECT * FROM pm WHERE conversation_uuid='e88c5ee7-4c4e-11de-a8c8-ee89e0c74506'
id conversation_uuid sender_id subject datetime
438655 e88c5ee7-4c4e-11de-a8c8-ee89e0c74506 2701 lösenord 2009-01-11 22:27:32
438656 e88c5ee7-4c4e-11de-a8c8-ee89e0c74506 1 lösenord 2009-01-11 23:02:24
438657 e88c5ee7-4c4e-11de-a8c8-ee89e0c74506 2701 lösenord 2009-01-12 21:11:01
438658 e88c5ee7-4c4e-11de-a8c8-ee89e0c74506 1 lösenord 2009-01-13 14:25:31
438659 e88c5ee7-4c4e-11de-a8c8-ee89e0c74506 2701 lösenord 2009-01-13 16:54:32
SELECT * FROM pm2user WHERE conversation_uuid='e88c5ee7-4c4e-11de-a8c8-ee89e0c74506'
conversation_uuid user_id folder_id invited last_viewed
e88c5ee7-4c4e-11de-a8c8-ee89e0c74506 1 1 2000-01-01 00:00:01 2009-05-29 14:47:48
e88c5ee7-4c4e-11de-a8c8-ee89e0c74506 2701 1 2000-01-01 00:00:01 2009-05-29 14:47:48