Execute SELECT within a CASE

Question

Execute SELECT within a CASE

Navigation

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

5

I am using php to make a SELECT of the database, however I need to select check if one of the fields has been filled in and, if so, execute a new SELECT of another table.

Basically the idea is that I have a coupon table:

tabela: cadastro_cupom
id | nome_cupom | valor_cupom | id_cliente

When the id_cliente field is populated, I need to get its name from another table:

tabela: cadastro_cliente
id | nome_cliente | idade_cliente | etc...

I'm currently using this SQL:

SELECT id, nome_cupom, valor_cupom, id_cliente
FROM cadastro_cupom
    CASE WHEN id_cliente IS NOT NULL THEN
        SELECT b.nome_cliente
        FROM cadastro_cupom a, cadastro_cliente b
        WHERE a.id_cliente = b.id
    END

But I can not get the results and I do not see any errors.

php sql

asked by anonymous 14.07.2016 / 16:25

2 answers

How to query with filter in Firebase? What possible values in document.readyState?

score 1 · Answer 1

The question already has an answer in the comments, but here is an answer if someone comes across the same question in the future

SELECT Cup.id, 
       Cup.nome_cupom, 
       Cup.valor_cupom, 
       Cli.id_cliente
FROM cadastro_cupom Cup
LEFT JOIN cadastro_cliente Cli
  ON Cli.id = Cup.id_cliente

This query will return to all coupons, the id of the respective client, if it exists. In case the customer is not filled the query lists only the details of the coupon.

If you want to list only the coupons for which an associated client exists, replace LEFT JOIN with INNER JOIN .

score 0 · Answer 2

So I understand you want to retrieve the client's name. The answers above already provide you with a path to what you need. Anyway I'll just add a possibility by assuming id_cliente is an index in your entities:

SELECT id, nome_cupom, valor_cupom, id_cliente, 
     (SELECT nome_cliente FROM cadastro_cliente c 
       WHERE c.id_cliente = a.id_cliente) nome_cliente 
 FROM cadastro_cupom 
WHERE id_cliente is NOT NULL -- se quiser apenas cupons com clientes.
;

I suggest checking the cost of this and other queries.