Mind gem Mind gem - 1 month ago 6
SQL Question

Query join tables

I have the following three tables:

Medaltypes

id name description
6 Medalname Right on!
7 Medalname2 Aswsome
8 Medalname3 Allright


Medals

id medaltype userref
1 6 MYUSERNAME
2 7 MYUSERNAME
3 6 OTHERUSER
4 6 OTHERUSERALSO


Userprofiles

userid userref
1 MYUSERNAME
2 OTHERUSER
3 OTHERUSERALSO


What I want is a result of all medaltypes for a specific userid including the onces earned and the onces still to be earned. Like for userid 1 this would be as outcome:

Medalname - Earned
Medalname2 - Earned
Medalname3 - To be earned


I can get a list with the Medaltypes and Medals with a left join but as soon as I join in (let alone query on specific userid) Userprofiles it reduces the list.

How should this query look?

Answer

You want to use a LEFT JOIN, but the condition on the user needs to go in the ON clause:

select ml.name,
       (case when m.id is not null then 'EARNED' else 'TO BE EARNED'
        end)
from medaltypes ml left join
     medals m
     on m.medaltype = ml.id and
        m.userref = 'MYUSERNAME';

Your data structure is awkward. Medals should not contain userref; it should contain the id of the user.

Comments