tables
------

message
- id
- created
- sender
- content

user_message
- id
- message_id
- user_id
- read

global_message
- id
- message_id


all read messages
-----------------

select
  m.*
from
  user_message um
  inner join message m on (um.message_id = m.id)
  inner join global_message gm on (gm.message_id = m.id)
where
  um.user_id = $user;

all unread messages
--------------------
select
  *
from
  (select
    distinct message_id
   from
     (select message_id from user_message where user_id = $user
      union
      select message_id from global_message)
  ) ids
  inner join message m on (ids.message_id = m.id)
  left join user_message um on (m.id = um.message_id)
where
  um.read is null;

all messages
------------
select
  *
from
  (select
    distinct message_id
   from
     (select message_id from user_message where user_id = $user
      union
      select message_id from global_message)
  ) ids
  inner join message m on (ids.message_id = m.id)
  left join user_message um on (m.id = um.message_id)