CODEWITHSUNDEEP
mysqlsql
KRA
KRA

Reputation: 32

if else on sql using mysql

How can I do if else statement on sql? I want to execute this query.

SELECT *
FROM mouvements,
     articles,
     clients,
     fournisseurs
WHERE mouvements.id_art = articles.id_art
  AND IF(mouvements.type_mouv = "true",
         mouvements.id_fournisseur = fournisseurs.id ,
         mouvements.id_client = clients.id_cli)

Upvotes: 0

Views: 73

Answers (1)

A. Bandtock
A. Bandtock

Reputation: 1241

It's possible to do this without much complexity by using purely logical operators:

SELECT *
FROM mouvements,
     articles,
     clients,
     fournisseurs
WHERE mouvements.id_art = articles.id_art
AND (
  (mouvements.type_mouv = "true" AND mouvements.id_fournisseur = fournisseurs.id)
  OR (mouvements.type_mouv <> "true" AND mouvements.id_client = clients.id_cli)
)

However, it is generally best practice to use explicit JOINs like so:

SELECT * 
FROM mouvements m
LEFT JOIN articles a ON (m.id_art = a.id_art)
LEFT JOIN clients c ON (m.type_mouv <> "true" AND m.id_client = c.id_clie)
LEFT JOIN fournisseurs f ON (m.type_mouv = "true" AND m.id_fournisseur = f.id)

Note that you can also use an INNER JOIN over a LEFT JOIN to exclude results where the conditions of the join are not fulfilled.

A further note on best practice: If you are selecting all columns (SELECT *) while joining tables, you'll quickly find yourself with a lot of data, it's better for maintenance and performance to limit your SELECT to only the columns you need, with explicit table identifiers, e.g. SELECT m.id_art, m.id_fournisseur.

Upvotes: 1

Related Questions