CODEWITHSUNDEEP
mysqlsql
Jaime Cuellar
Jaime Cuellar

Reputation: 474

Errors While Using IF statement in sql query

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

Upvotes: 0

Views: 86

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 1

Fahad Anjum
Fahad Anjum

Reputation: 1256

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;

Upvotes: 2

Related Questions