Home / Questions / 210934
Accepted
pxs
Asked: 2020-11-06 12:17:25 +0800 CST 2020-11-06 12:17:25 +0800 CST

sql query (mysql) and timestamp

  • 772

I want to query the results in a range of dates and perform joins. Currently my query is:

SELECT  fichapagos.cantidad, 
users.name as nombreRecibe, 
modopagos.nombre as tipoDePago, 
clientes.nombre as elCliente,
conceptopagos.concepto as elConcepto
FROM fichapagos, users,modopagos,clientes,conceptopagos 

WHERE MONTH(fichapagos.created_at) = MONTH(CURRENT_DATE()) 
AND YEAR(fichapagos.created_at) = YEAR(CURRENT_DATE())

LEFT JOIN modopagos  
ON fichapagos.tipoPago = modopagos.id 
LEFT JOIN clientes
ON fichapagos.organizacionPara = clientes.id
LEFT JOIN conceptopagos
ON fichapagos.conceptoPago = conceptopagos.id
LEFT JOIN users
on fichapagos.nombreRecibe = users.id

But it throws me an error:

#1064 - Algo está equivocado en su sintax cerca 'LEFT JOIN modopagos ON fichapagos.tipoPago = modopagos.id LEFT JOIN cliente' en la linea 11

The created_at field is timestamp type, if I don't have the joins the query works. If I don't have WHERE the query works. what am I doing wrong? Thanks.

mysql

2 Answers

  1. Try this statement:

    SELECT  fichapagos.cantidad, 
users.name as nombreRecibe, 
modopagos.nombre as tipoDePago, 
clientes.nombre as elCliente,
conceptopagos.concepto as elConcepto
FROM fichapagos, users,modopagos,clientes,conceptopagos 

LEFT JOIN modopagos  
ON fichapagos.tipoPago = modopagos.id 
LEFT JOIN clientes
ON fichapagos.organizacionPara = clientes.id
LEFT JOIN conceptopagos
ON fichapagos.conceptoPago = conceptopagos.id
LEFT JOIN users
on fichapagos.nombreRecibe = users.id

WHERE MONTH(fichapagos.created_at) = MONTH(CURRENT_DATE()) 
AND YEAR(fichapagos.created_at) = YEAR(CURRENT_DATE())

    We WHEREuse it after the LEFT JOIN.

    • 0
  2. Best Answer

    Reviewing your query you have the following drawbacks:

    1. The use of the WHERE must be after relationships, in this case your LEFT JOINS .
    2. In the FROM you must indicate a single table, and by recommendation and good practice, you should start from the table with the largest number of records.

    With this, your query would be as follows:

    SELECT fichapagos.cantidad, 
    users.name as nombreRecibe, 
    modopagos.nombre as tipoDePago, 
    clientes.nombre as elCliente,
    conceptopagos.concepto as elConcepto
FROM fichapagos  --  Tabla según yo, más extensa.
LEFT JOIN modopagos  
    ON fichapagos.tipoPago = modopagos.id 
LEFT JOIN clientes
    ON fichapagos.organizacionPara = clientes.id
LEFT JOIN conceptopagos
    ON fichapagos.conceptoPago = conceptopagos.id
LEFT JOIN users
    on fichapagos.nombreRecibe = users.id
WHERE MONTH(fichapagos.created_at) = MONTH(CURRENT_DATE())  --  Cambio de ubicación.
    AND YEAR(fichapagos.created_at) = YEAR(CURRENT_DATE())

    Try it and tell us. Greetings.

    • 0