1

I am doing a full join query from two tables.

SELECT  
i.idinasistencia,  i.fecha, i.valor, i.justificado,
h.id, p.nombres, p.apellido_paterno, p.apellido_materno,
p.rut_persona
FROM historialalumno h 
FULL JOIN inasistencias i
    ON i.idhistorialalumnofk = h.id
INNER JOIN alumno a 
    ON a.idalumno = h.idalumnofk
LEFT JOIN persona p 
    ON p.idpersona= a.idpersonafk
WHERE h.idcursofk = 8
    AND h.idcolegiofk = 1
    AND h.idanioacademicofk = 1
    **AND i.fecha = '06/07/2016'**

But I need to filter the rows by date (fecha), that field exist only in one of the tables, then the query result only bring the rows from one table. But I need to bring all the rows from both tables, no matter if date (fecha) no exist in the other table. I know the columns possibly show 'null' value but is ok for me. How could I solve this problem?

2

You should be able to get that by simply adding the additional criteria to the full join instead of the where clause.

 on i.idhistorialalumnofk = h.id and i.fecha = '06/07/2016'

it seems odd to be needing a FULL join here but we don't really have much info so that might actually be the case.

|improve this answer|||||
1

I would use a subquery:

...
FROM historialalumno h
FULL JOIN (
   SELECT * FROM inasistencias
   WHERE  fecha = '2016-06-07'
   ) i ON i.idhistorialalumnofk = h.id
...

And don't use ambiguous date formats. Use unambiguous ISO format.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.