<p>I want to maintain a list of global messages that will be displayed to all users of a web app. I want each user to be able to mark these messages as read individually. I've created 2 tables; <code>messages (id, body)</code> and <code>messages_read (user_id, message_id)</code>.</p>

<p>Can you provide an sql statement that selects the unread messages for a single user? Or do you have any suggestions for a better way to handle this?</p>

<p>Thanks!</p>

[{'answer_id': 76702, 'author': 'cynicalman', 'author_id': 410, 'author_profile': 'https://Stackoverflow.com/users/410', 'pm_score': 3, 'selected': False, 'text': '<p>Well, you could use</p>\n\n<pre><code>SELECT id FROM messages m WHERE m.id NOT IN(\n    SELECT message_id FROM messages_read WHERE user_id = ?)\n</code></pre>\n\n<p>Where ? is passed in by your app.</p>\n'}, {'answer_id': 76733, 'author': 'Leigh Caldwell', 'author_id': 3267, 'author_profile': 'https://Stackoverflow.com/users/3267', 'pm_score': 0, 'selected': False, 'text': "<p>Something like:</p>\n\n<pre><code>SELECT id, body FROM messages LEFT JOIN\n  (SELECT message_id FROM messages_read WHERE user_id = ?)\n  ON id=message_id WHERE message_id IS NULL \n</code></pre>\n\n<p>Slightly tricky and I'm not sure how the performance will scale up, but it should work.</p>\n"}, {'answer_id': 78715, 'author': 'Bennor McCarthy', 'author_id': 14451, 'author_profile': 'https://Stackoverflow.com/users/14451', 'pm_score': 3, 'selected': True, 'text': '<p>If the table definitions you mentioned are complete, you might want to include a date for each message, so you can order them by date.</p>\n\n<p>Also, this might be a slightly more efficient way to do the select:</p>\n\n<pre><code>SELECT id, message\nFROM messages\nLEFT JOIN messages_read\n    ON messages_read.message_id = messages.id\n    AND messages_read.[user_id] = @user_id\nWHERE\n    messages_read.message_id IS NULL\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/76680', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/13636/']