Select All NULL and NOT NULL Records

Question

I have two tables:

Medals and Loja_medals.

I want to bring in just one SELECT all medals and create a column to put your situation. Would be two status, Yes and In the.

The structures:

MEDALS

ID
MEDALHA

LOJA_MEDALHAS

ID
NOME
ID_MEDALHA

Here’s what I’m doing SELECT down below:

SELECT 
    ID_LOJA,
    MEDALHAS.ID,
    MEDALHA,
    CASE LOJA_MEDALHA.ID_MEDALHA
    WHEN NULL THEN 'NÃO'
    ELSE 'SIM' END AS SITUACAO
FROM
    MEDALHAS
LEFT OUTER JOIN
    LOJA_MEDALHA
ON
    LOJA_MEDALHA.ID_MEDALHA = MEDALHAS.ID OR LOJA_MEDALHA.ID_MEDALHA <> MEDALHAS.ID
WHERE
    ID_LOJA = 1

This consultation is bringing all Yes. Since I only added a medal to the store.

I want to bring them all so I can display the lit or erased medal on my page depending on their status.

Answer 1

To the work with NULL values in Mysql, use IS NULL and IS NOT NULL to verify this condition:

WHEN LOJA_MEDALHAS.ID_MEDALHA IS NULL

Answer 2

I got.

SELECT 
    ID_LOJA, 
    MEDALHAS.ID, 
    ID_MEDALHA,
    MEDALHA,
    CASE WHEN LOJA_MEDALHAS.ID_MEDALHA IS NULL
    THEN 'NÃO'
    ELSE 'SIM' END AS SITUACAO
FROM 
    MEDALS 
LEFT JOIN 
    LOJA_MEDALHAS ON LOJA_MEDALHAS.ID_MEDALHA = MEDALHAS.ID
AND 
    ID_LOJA = 1