Request to give out right badges and blurry the rest

Question

I have a table badges that lists all available badges (badge_id, name, description, ...), a table users that lists all users (user_id, name, ...) and a table users_badges that saves all earned badges (id, badge_id, user_id).

If user 1 earns badge 5, an entry (x, 5, 1) is put into users_badges (a user can only accquire each badge once).

To show each user which badges she has earned so far and which are still missing, I want to list her all badges whereas earned badges are colorful and non-earned badges gray.

For this I need an appropriate SQL query - and that's what I'm struggling with. A possible output would be a distinct list with all badges and the user_id of the particular user next to it; or NULL if she hasn't earned the badge yet.

First try:

SELECT * FROM badges
LEFT JOIN users_badges ON badges.id = users_badges.badge_id
WHERE users_badges.user_id = ".$row['user_id']." OR users_badges.user_id IS NULL
ORDER BY badges.id

Problem: This works only for the user that owns all the badges of the other users or more as the WHERE kicks out badges owned by others only.

Second try:

SELECT * FROM badges
LEFT JOIN users_badges ON badges.id = users_badges.badge_id
ORDER BY badges.id

Problem: Badges are listed multiple times if owned by multiple users. Adding a DISTINCT would not solve the problem as the rows are already distinct (different user ids). Maybe it is possible to go from this approach and kick out all rows with user_ids other than the requested one, but only if she already has earned to badge (to ensure all non-owned badges are listed, too (in grey)?

Answer 1

Making some assumptions about the structure of your data, I created dummy tables for this example:

create table #badges
(
    badge_id int,
    [name] varchar(50),
    [description] varchar(50)
)

create table #users
(
    [user_id] int,
    [name] varchar(50)
)

create table #users_badges
(
    id int,
    badge_id int,
    [user_id] int
)

insert into #badges
values
    (1,'Starter Badge','It''s your first time.'),
    (2,'Rookie Badge','You''re just getting started.'),
    (3,'Intermediate Badge','Now you''re starting to get the hang of this.'),
    (4,'Expert Badge','You are a master!')

insert into #users
values
    (1,'John Smith'),
    (2,'Alice Johnson'),
    (3,'Phillip Black'),
    (4,'Sarah Goodwell'),
    (5,'Ian Hunter')

insert into #users_badges
values
    (1,1,1),
    (2,1,2),
    (3,1,3),
    (4,1,4),
    (5,2,1),
    (6,2,2),
    (7,2,4),
    (8,3,5),
    (9,4,1),
    (10,4,2)

I think this is the result you're looking for (here, specifically looking at the User with a user_id of 1 - John Smith):

select 
    b.*,  
    case when ub.id is not null then 'Earned' else null end as badgeStatus
from #badges b
left join #users_badges ub on b.badge_id = ub.badge_id and ub.[user_id] = 1

This lists all the badges and shows which ones John has earned:

/------------------------------------------------------------------------|--------\
| id | name               | description                                  | status |
|----|--------------------|----------------------------------------------|--------|
|  1 | Starter Badge      | It's your first time.                        | Earned |
|  2 | Rookie Badge       | You're just getting started.                 | Earned |
|  3 | Intermediate Badge | Now you're starting to get the hang of this. | NULL   |
|  4 | Expert Badge       | You are a master!                            | Earned |
\---------------------------------------------------------------------------------/