Run SELECT within a CASE

Asked

Viewed 1,081 times

5

I’m using the php to make a SELECT of the database, but I need the select check that one of the fields has been filled in and, if yes, run a new SELECT from another table.

Basically the idea is that I have a coupon table:

tabela: cadastro_cupom
id | nome_cupom | valor_cupom | id_cliente

When the field id_cliente is filled in, I need to get his name from another table:

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

I am 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’m not getting the results and also I don’t see any errors.

php sql

  • Use the left join just as SELECT a.*, b.* FROM cadastro_cupom a LEFT JOIN cadastro_cliente b ON a.id_cliente = b.id would not solve ?

  • 1

    @Williamnovak seems to have solved yes. I’m new to SQL and PHP so I didn’t know about this function.. If you could come up with a response to the matter, I would appreciate it :)

2 answers

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 for all coupons, the id of the respective client, if it exists. In case the client is not filled in the query lists only the coupon details.

If you want to list only the coupons for which there is an associated customer, replace the LEFT JOIN by a INNER JOIN.

0

From what I understand you want to retrieve the customer’s name. The above answers already give you a path to what you need. Anyway I’ll just add a possibility id_cliente be an index in its 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 query and other.

Browser other questions tagged php sql

You are not signed in. Login or sign up in order to post.