How to use IF to choose a JOIN TABLE in a query SQL?-CodePudding

I have 3 tables. Debiti, Eventi and Parametri. In my Debiti table, I have id_evento and id_parametro fields. When the id_evento field is greater than zero, I need to do a join with the Eventi table. When the id_parametro field is greater than zero, I need to do a join with the Parametri table. The id_event and id_parametro fields will never be greater than zero at the same time. I think I need to use an IF or something similar. I was wondering if anyone could help me solve this problem.

SELECT  debiti.*, eventi.*, parametri.*    
FROM debiti 
INNER JOIN parametri ON debiti.id_parametro = parametri.id_parametro 
INNER JOIN eventi ON debiti.id_evento = eventi.id_evento

When executing this query, only one of the joins works.

In short, I need that

if id_evento > 0 then join Debiti with Eventi. if id_parametro > 0 then join Debiti with Parametri

Thank you.

CodePudding user response：

You should use LEFT JOIN:

SELECT  debiti.*, eventi.*, parametri.*    
FROM debiti 
LEFT JOIN parametri ON debiti.id_parametro = parametri.id_parametro 
LEFT JOIN eventi ON debiti.id_evento = eventi.id_evento

and optionally you can add the checks for the id's >0 (But that should not change the output when there is no 0 value in `parameteri' or 'eventi') :

SELECT  debiti.*, eventi.*, parametri.*    
FROM debiti 
LEFT JOIN parametri ON debiti.id_parametro>0 AND debiti.id_parametro = parametri.id_parametro 
LEFT JOIN eventi ON debiti.id_evento>0 AND debiti.id_evento = eventi.id_evento