Select All NULL and NOT NULL Records

Question

Select All NULL and NOT NULL Records

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have two tables:

Medals and Grocery Store .

I want to bring in just a% of all medals and create a column to put your situation. There would be two statuses, Yes and No .

The structures:

MEDALS

ID
MEDALHA

MEDIA SHOP

ID
NOME
ID_MEDALHA

I'm doing the following SELECT 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 query is bringing all Yes . Being that I added only one medal to the store.

I want to bring all of them in order to display the medal on or off on my page depending on your status.

mysql

asked by anonymous 07.03.2016 / 13:30

2 answers

1

working with NULL values in MySQL, use IS NULL and IS NOT NULL to verify this condition:

WHEN LOJA_MEDALHAS.ID_MEDALHA IS NULL

07.03.2016 / 13:47

Specific element update in a Mongoose array Show profit or loss in negative percentage

score 0 · Accepted Answer

I did it.

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