How to check if relationship exists - Many to many relationship

I need your help to figure out how to make a query. My idea was to build a credit system to reward users. The administrator enters the description of credits (e.g., user is subscribed to the newsletter, he had post at least 10 comments...). I have 3 tables:

• users (id and name),
• rewards (id and descrip开发者_开发知识库tion),
• rewards_users (id_reward, id_user).

I would pull out a summary html table whose rows are formed by the user's name and a Y or an N depending on whether there is a relationship between the user and the reward.

---

use the following query to show all of the rewards for a specific user

SELECT r.description, CASE WHEN ru.user_id IS NULL THEN 'N' ELSE 'Y' END awarded
FROM rewards r
LEFT JOIN rewards_users ru ON r.reward_id=ru.reward_id AND ru.user_id = 1

and use it as a sub query to get the users details too

e.g:

SELECT u.user_id, u.user_name, ua.award, ua.awarded
FROM users u,
     (
     SELECT r.description, CASE WHEN ru.user_id IS NULL THEN 'N' ELSE 'Y' END awarded
     FROM rewards r
     LEFT JOIN rewards_users ru ON r.reward_id=ru.reward_id AND ru.user_id = u.user_id
     ) ua
WHERE u.userid=1

Note: not tested.

---

Sounds to me like you need a LEFT JOIN between your users table and the rewards_users table and then to handle instances of null rewards_id:

select u.id as users_id,
u.name as users_name,
case when ur.rewards_id is null then 'N' else 'Y' end as hasRewards
from users u left join users_rewards ur
on u.id = ur.users_id;