sql query (mysql) and timestamp

Question

sql query (mysql) and timestamp

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I want to consult 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 I bounced 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 do not have the joins the query works. If I do not have WHERE the query works. what am I doing wrong? Thanks.

mysql

asked by pxs 05.11.2018 в 21:17

source

2 answers

0

Try this sentence:

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())

The WHERE we use after the LEFT JOIN .

answered by 05.11.2018 в 21:31

MySQL - join 2 or more tables with null fields Help I try to create a record of an autoincrement value which is a primary key and is a foreign key

score 0 · Accepted Answer

Checking your query has the following disadvantages:

The use of the WHERE should be after the relations, in this case of your LEFT JOINS .

In the FROM you must indicate a single table, and for recommendation and good practices, 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.