Error in query

Question

Error in query

Navigation

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

0

Good day, I'm doing a query in PHP to get the data depending on the date of the records, the detail is that the query does not work, I'm not sure that the syntax of the query in the WHERE part this correct since I use an INNER JOIN to obtain information from two tables, they could indicate me if the query is correct and if not, they could tell me in which part I am wrong. Annex code:

$consulta = "SELECT empleado.nomina, empleado.nombre, empleado.depto, comentarios.comentario, comentarios.fecha FROM empleado WHERE fecha < '$fecha' INNER JOIN comentarios on empleado.nomina=comentarios.nomina";

mysql php

asked by Xavi 08.11.2018 в 19:58

source

2 answers

How to get the MAX and AVG in Oracle? [closed] activate a swich with a select

score 0 · Answer 1

The INNER JOIN is part of the FROM clause, it should not go in WHERE . There are several ways to structure a query to generate the intersection of the table, at this moment you may be confusing two of them:
We can do it with a INNER JOIN :

SELECT empleado.nomina,
       empleado.nombre,
       empleado.depto,
       comentarios.comentario,
       comentarios.fecha
  FROM empleado    INNER JOIN 
       comentarios ON empleado.nomina=comentarios.nomina
 WHERE fecha < '$fecha';

The other way is by generating a Cartesian product and filtering the rows with the clause WHERE :

SELECT empleado.nomina,
       empleado.nombre,
       empleado.depto,
       comentarios.comentario,
       comentarios.fecha
  FROM empleado   , -- la coma produce un producto cartesiano entre ambas tablas
       comentarios
 WHERE empleado.nomina=comentarios.nomina --los registros se filtran en el where
       fecha < '$fecha';

Both styles are valid, but I recommend that if you use one you stay with that for the whole project to be consistent, in addition to formatting your query better, SQL is indifferent to the blanks but people do not and sometimes it is complicated to read querys of a single line.

score 0 · Answer 2

With this query you get the result you expect the INNER JOIN goes within FROM with the WHERE you can make filters (conditions)

SELECT e.nomina, 
    e.nombre, 
    e.depto, 
    c.comentario, 
    c.fecha 
        FROM empleado AS e
            INNER JOIN comentarios AS c ON e.nomina = c.nomina 
        WHERE c.fecha < '$fecha'

Recommendation tries to use aliases in queries.