mySQL complex SELECT query?

Question

Here is my table structure:

users:

id | email

emails:

id | subject | body

user_emails:

user_id | email_id

It's very simple design. However, I need to select the first (lowest) emails.id for all users.id's that are not associated in user_emails.

To illustrate:

users:

id | email
1 | [email protected]
2 | [email protected]
3 | [email protected]

emails:

id | subject | body
1 | sub1 | body1
2 | sub2 | body2

user_emails:

user_id | email_id
1 | 1
1 | 2
2 | 1

As the data shows:

• User 1 received email 1 and 2 already so the select should exclude that user.
• User 2 only received email 1, so it should select user 2 and email 2.
• User 3 didn't receive any emails, so it should select user 3 and email 1.

I'll be doing this in PHP so any logic should be in written in PHP if it can't be done with mySQL alone.

Thanks in advance

EDIT: I should probably mention that there will be thousands of user ID's and hundreds of email id's. The script that will send out all these emails will run once daily. So, this should be as optimized as possible.

Answer 1

Could try the below:

SELECT * from users u 
LEFT JOIN users_emails e ON u.id=e.user_id 
LEFT OUTER JOIN emails m on m.id=1 
WHERE e.user_id is NULL

Or use the below if the id in emails not necessary to be 1 as above query.

SELECT * from users u 
LEFT JOIN users_emails e ON u.id=e.user_id 
LEFT OUTER join emails m on m.id=(SELECT id from emails order by id limit 1)   
WHERE e.user_id is NULL