Errors While Using IF statement in sql query

Question

I am trying to make a query, which returns tipo_id from a table, depending on the value of this I want to join with another table, for example if tipo_id is 1 I want to join with table called p_read if tipo_id i want to join tv_read

this is what I tried to do.

SELECT ec.id,ec.estado,fv.id,fv.num_factura,fv.importe,fv.iva,fv.total,fv.fecha_consumo_inicio,fv.fecha_consumo_fin,
fv.fecha_factura, fv.fichero, c.total, l.tipo_id, lp.id_consumo FROM aldroges8.factura_venta fv
INNER JOIN aldroges8.lectura l ON fv.id=l.facturaVenta_id
INNER JOIN aldroges8.factura_cobro fc ON fc.facturaventa_id = fv.id
INNER JOIN aldroges8.cobros c ON c.id=fc.cobros_id
INNER JOIN aldroges8.estado_cobros ec ON ec.id = c.estado
IF (l.tipo_id=1)
 INNER JOIN aldroges8.lectura_potencia lp ON l.id=lp.id
ELSE IF (l.tipo_id =3)
 INNER JOIN aldroges8.lectura_tv_gas lp ON lp.id=l.id 
WHERE fv.factura_enviada=1 AND fv.suministro_id=:id_contrato ORDER BY fv.fecha_factura DESC;

But i am getting this error.

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF (l.tipo_id==1)
 INNER JOIN aldroges8.lectura_potencia lp ON l.id=lp.id
ELSE' at line 7

So I want to know if there is a way on doing this if staments on a query, or do I need to make another query with tipo_id, thanks in advance

You can't use IF statements in a query. Do left joins instead. — jarlh
– jarlh, Commented May 8, 2018 at 9:22
@FahadAnjum. we can use if statements in SQL. am very sure of that — kishea
– kishea, Commented May 8, 2018 at 9:25
you say use something like LEFT JOIN aldroges8.lectura_potencia lp ON l.id=lp.id LEFT JOIN aldroges8.lectura_tv_gas lp ON lp.id=l.id ? @jarlh — Jaime Cuellar
– Jaime Cuellar, Commented May 8, 2018 at 9:25
@kishea yes we can use if condition. but only in select clause. — Fahad Anjum
– Fahad Anjum, Commented May 8, 2018 at 9:29

Fahad Anjum · Accepted Answer · 2018-05-08 09:38:10Z

2

SELECT ec.id,ec.estado,fv.id,fv.num_factura,fv.importe,fv.iva,fv.total,fv.fecha_consumo_inicio,fv.fecha_consumo_fin,
fv.fecha_factura, fv.fichero, c.total, l.tipo_id, 
/* Used case when statement to get the required result in that column */ 
case when l.tipo_id=1 then lp_1.id_consumo 
     when l.tipo_id=3 then lp_3.id_consumo end  as id_consumo
FROM aldroges8.factura_venta fv
INNER JOIN aldroges8.lectura l ON fv.id=l.facturaVenta_id
INNER JOIN aldroges8.factura_cobro fc ON fc.facturaventa_id = fv.id
INNER JOIN aldroges8.cobros c ON c.id=fc.cobros_id
INNER JOIN aldroges8.estado_cobros ec ON ec.id = c.estado
left join  aldroges8.lectura_potencia lp_1 ON l.id=lp_1.id
left join aldroges8.lectura_tv_gas lp_3 ON lp_3.id=l.id 
WHERE fv.factura_enviada=1 AND fv.suministro_id=:id_contrato ORDER BY fv.fecha_factura DESC;

edited May 8, 2018 at 9:38

answered May 8, 2018 at 9:24

Fahad Anjum

1,2561 gold badge10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

MatBailie Over a year ago

Should include in your answer a clarification that you're assuming lp_1 and lp_3 have 0 or 1 matching rows; thus justifying your LEFT JOIN and allowing you to elaborate that if they match many times the you get horrible duplication.

Jaime Cuellar Over a year ago

This made the query work, but I am not getting the right information, I am getting id_consumo null while in my table its not null

Fahad Anjum Over a year ago

@MatBailie Thanks. I will take that into consideration.

MatBailie Over a year ago

@JaimeCuellar : Please update your question with an example after reading this : stackoverflow.com/help/mcve By using a simplified example with dummy data you can show us which cases you're getting the wrong results for. (As it stands I don't believe you're getting a NULL results for a NOT NULL value in the table, something else is the actual problem : Such as l.tipo_id being 1 but the value you want being in lectura_tv_gas.)

MatBailie Over a year ago

@JaimeCuellar - Read the link I gave you, create an example that only includes what you need, raise a new question with that Minimal Complete Verifiable Example.

|

Gordon Linoff · Accepted Answer · 2018-05-08 10:49:31Z

I would write this with the condition in the on clause and then use coalesce() in the select:

SELECT ec.id, ec.estado, fv.id, fv.num_factura, fv.importe, fv.iva, 
       fv.total, fv.fecha_consumo_inicio, fv.fecha_consumo_fin,
       fv.fecha_factura, fv.fichero, c.total, l.tipo_id, 
       coalesce(lp_1.id_conumo, lp_3.id_consumo)  as id_consumo
FROM aldroges8.factura_venta fv INNER JOIN
     aldroges8.lectura l
     ON fv.id = l.facturaVenta_id INNER JOIN
     aldroges8.factura_cobro fc
     ON fc.facturaventa_id = fv.id INNER JOIN
     aldroges8.cobros c
     ON c.id = fc.cobros_id INNER JOIN
     aldroges8.estado_cobros ec
     ON ec.id = c.estado LEFT JOIN
     aldroges8.lectura_potencia lp_1
     ON l.id = lp_1.id AND l.tipo_id = 1 LEFT JOIN
     aldroges8.lectura_tv_gas lp_3
     ON lp_3.id = l.id AND l.tipo_id = 3
WHERE fv.factura_enviada = 1 AND
      fv.suministro_id = :id_contrato
ORDER BY fv.fecha_factura DESC;

The difference between doing the comparison in the ON verses in a CASE expression may seem subtle, but it can be important.

If there are multiple matches in either table, then putting the condition in the SELECT will result in duplicate rows.

Collectives™ on Stack Overflow

Errors While Using IF statement in sql query

2 Answers 2

14 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

14 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related