Postgresql – Full Join Query

join;postgresql

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?

Best Answer

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.

Related Solutions

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

That one caused me quite a bit of headache before I found a halfway elegant solution:

WITH x AS (
    SELECT BASE.id AS b_id
          ,BASE.start_date AS b_start_date
          ,BASE.end_date AS b_end_date
          ,CHILD.id AS c_id
          ,CHILD.start_date AS c_start_date
          ,CHILD.end_date AS c_end_date
          ,CASE WHEN DATE '2011-1-1' BETWEEN CHILD.start_date AND CHILD.end_date
                                     OR CHILD.id IS NULL THEN 0 ELSE 1 END AS c_hit
    FROM   BASE LEFT OUTER JOIN CHILD ON (BASE.ID=CHILD.ID)
    WHERE  BASE.ID = 1
    AND    DATE '2011-1-1' BETWEEN BASE.start_date AND BASE.end_date
    )
SELECT b_id, b_start_date, b_end_date
      ,c_id, c_start_date, c_end_date
FROM   x
WHERE  x.c_hit = 0

UNION ALL
SELECT b_id, b_start_date, b_end_date
      ,NULL, NULL,         NULL
FROM   x
GROUP  BY b_id, b_start_date, b_end_date
HAVING min(c_hit) = 1;

Of course, the better solution would be to get rid of the restrictions and adjust the JOIN in the base query. As @a_horse expressed it so poetically in the comments above:

the perils of ORM frameworks

Anyway, to solve the question at hand:
After selecting rows where BASE.id and BASE.start_date / BASE.end_date match, we have four different cases:

No child at all, so CHILD.id is NULL.
Child matches.
Child does not match and no other matching child.
Child does not match but other matching child.

This is hard to solve on one query level. You could do it with window functions and CASE statements and DISTINCT, in fact I had a basic working query, but it gets monstrous.

Using a CTE dramatically simplifies the syntax.
In the CTE:

Filter BASE on id and date range. Still includes rows with non-matching children.
Assign aliases for identical column names
Tag rows where child matches the date

In the final SELECT:

Return all rows with matching children
Return one row where no matching children exist, with NULLs for child-columns.

Here is working demo on sqlfiddle.com for those who want to toy with it.

Sql-server – Join Calender Table

I think you want (tested at SQL-Fiddle-1):

SELECT  the_id    = dd.the_id, 
        the_day   = ct.the_day,
        the_count = COALESCE(SUM(dt.the_count), 0)
FROM 
        calendartable AS ct
    CROSS JOIN
        ( SELECT DISTINCT the_id 
          FROM datatable
          WHERE the_date BETWEEN @mindate 
                             AND @maxdate
        ) AS dd
    LEFT JOIN 
        datatable AS dt
            ON  dt.the_date = ct.the_day
            AND dt.the_id = dd.the_id 
WHERE 
        ct.the_day BETWEEN @mindate 
                        AND @maxdate
GROUP BY 
        dd.the_id, ct.the_day
ORDER BY 
        the_id, the_day ;

It can also be written like this (SQL-Fiddle-2):

SELECT  the_id    = dd.the_id, 
        the_day   = ct.the_day,
        the_count = COALESCE(
            ( SELECT SUM(dt.the_count)
              FROM   datatable AS dt
              WHERE  dt.the_date = ct.the_day
                AND  dt.the_id = dd.the_id     
            )               
                            , 0)
FROM 
        calendartable AS ct
    CROSS JOIN
        ( SELECT DISTINCT the_id 
          FROM datatable
          WHERE the_date BETWEEN @mindate 
                             AND @maxdate
        ) AS dd
WHERE 
        ct.the_day BETWEEN @mindate 
                        AND @maxdate
ORDER BY 
        the_id, the_day ;

Best Answer

Related Solutions

LEFT JOIN conversion to INNER JOIN, can’t change FROM clause

Sql-server – Join Calender Table

Related Question