I am working on Private Message system. This is the query for "Saved massages" which can contain both in-going and out-going messages.
profile_settings:
profile_id | timezone_offset | timezone_dst
100000 | 1 | 1
100001 | 0 | 0
profiles:
profile_id | profile_name
100000 | testuser
100001 | anotherone
private_messages:
message_id | message_date | profile_id_sender | profile_id_receiver | ....
1 | 2006-07-22 15:32:56 | 100000 | 100001 | ...
2 | 2006-07-22 15:38:06 | 100001 | 100000 | ...
SELECT private_messages., profiles.profile_name AS sender_name, profiles_temp.profile_name AS receiver_name,
ADDTIME(private_messages.message_date, SEC_TO_TIME((profile_settings.timezone_offset + profile_settings.timezone_dst) 3600)) AS message_date_receiver,
ADDTIME(private_messages.message_date, SEC_TO_TIME((profile_settings_temp.timezone_offset + profile_settings_temp.timezone_dst) *3600)) AS message_date_sender
FROM private_messages
INNER JOIN profiles ON private_messages.profile_id_sender = profiles.profile_id
INNER JOIN profile_settings ON private_messages.profile_id_receiver = profile_settings.profile_id
INNER JOIN profile_settings AS profile_settings_temp ON private_messages.profile_id_sender = profile_settings_temp.profile_id
INNER JOIN profiles AS profiles_temp ON private_messages.profile_id_receiver = profiles_temp.profile_id
WHERE (private_messages.profile_id_receiver = 100000 OR private_messages.profile_id_sender = 100000)
ORDER BY message_date DESC
It works as it should but I think 4 inner joins is a bit much...
