How to join two tables on a condition that may not occur?

Question

I have two tables

• Table User, has ID field
• Table Options, has userId , optionName, value

How to select users that have optionName = 'email' and value = 1 or have no entries for email in the Options table?

Answer 1

Try this:

select * from User u inner join Option o on u.id = o.userid
where (o.optionName = 'email' and o.value = 1) or (o.optionName <> 'email')

Answer 2

THis might help you:

"SELECT * from users u left join options o on u.id = o.userid where (o.optionName = 'email' and o.value = 1) or o.userid is null; ";