Home / Questions / 317041
Accepted
J. Rodríguez
Asked: 2020-12-19 07:32:14 +0800 CST 2020-12-19 07:32:14 +0800 CST

Filter table data before doing Left Join

  • 772

I have been presented with a case that I do not know how to solve, I am trying to filter certain data from several tables, in this case I will only put a sample of the use case that I do not know how to solve.

Table ESTATUSEMPLEADO: (It has many more fields, it is just a sample)

╔══════════╦═══════╦══════╗
║ Codigo   ║ Nombre║ .... ║
╠══════════╬═══════╬══════╣     
║ 1        ║ Juan  ║ .... ║
║ 2        ║ José  ║ .... ║
║ 3        ║ Maria ║ .... ║
║ 4        ║ Joel  ║ .... ║
║ 5        ║ Ramón ║ .... ║
║ 6        ║ Teresa║ .... ║
║ ....     ║ ....  ║ .... ║
╚══════════╩═══════╩══════╝

Table NOTIFICACION: (In this the field Codigocan be repeated, it is a Details table...)

╔══════════╦═══════╦═══════╦════════════╗ 
║ Codigo   ║ Estado║ UserN ║ OtroCampo  ║
╠══════════╬═══════╬═══════╬════════════╣
║ 4        ║ N     ║ admin ║ Dato1      ║
║ 1        ║ S     ║ admin ║ Dato2      ║
║ 6        ║ N     ║ admin ║ Dato3      ║
║ 1        ║ N     ║ otro  ║ Dato4      ║
╚══════════╩═══════╩═══════╩════════════╝

SELECT e.Codigo, e.Nombre,
       Leida = case when n.Estado = 'S' then 'S' else 'N' end,
       n.OtroCampo,
FROM ESTATUSEMPLEADO e 
LEFT JOIN NOTIFICACION n  
ON e.Codigo = n.Codigo AND n.UserN = 'admin' AND n.Estado in('N')
WHERE e.AlgunCampo = ....

Desired result:

╔══════════╦═════════╦═══════╦════════════╗ 
║ Codigo   ║ Nombre  ║ Leida ║ OtroCampo  ║
╠══════════╬═════════╬═══════╬════════════╣
║ 2        ║ José    ║ N     ║ NULL       ║
║ 3        ║ Maria   ║ N     ║ NULL       ║
║ 4        ║ Joel    ║ N     ║ Dato1      ║
║ 5        ║ Ramón   ║ N     ║ NULL       ║
║ 6        ║ Teresa  ║ N     ║ Dato3      ║
╚══════════╩═════════╩═══════╩════════════╝

With this select it is showing me all the data of the table ESTATUSEMPLEADOthe filter UserNworks correctly, when I also want to filter by Estadoit does not filter the data correctly, it must exclude Codigo = 1me since I am filtering that Estado = 'N'instead it is showing it to me in the result and shows the field Leida = 'N'.

  • I am not using WHERE to filter the table NOTIFICACIONbecause this table may or may not contain records, and if I do it in the Where, it will only show me the data that is in this table.

  • WHERE e.SomeField = .... (This is because I also have some conditions but they belong to the table ESTATUSEMPLEADO)

  • I use n.Estado in('N')since there are times when I have to filter the Sand N.

I want to get all the data from the table ESTATUSEMPLEADOand do the left join with the table NOTIFICACION, to filter the data and only show me the data that meets the specified conditions.

Environment: SQL Server 2008 R2

If my question is not entirely clear, please ask questions in the comments. Thanks.

sql-server

2 Answers

  1. Best Answer

    Something like that:

    SELECT  e.Codigo, 
        e.Nombre,
        Leida = case when n.Estado = 'S' then 'S' else 'N' end,
         n.OtroCampo
    FROM ESTATUSEMPLEADO e 
    LEFT JOIN NOTIFICACION n  
        ON e.Codigo = n.Codigo 
        AND n.UserN = 'admin' 
    WHERE isnull(n.Estado, 'N') = 'N'

    We take the status filter to whereand consider the eventual NULLones due to non-existence of rows in NOTIFICACIONasN

    • 3

  2. To deal with comparisons of the set on the right you can explicitly implement that the field is null.

    SELECT E.Codigo
 , E.Nombre
 , ISNULL(N.ESTADO,'N') as Leída
 , N.OTROCAMPO as OtroCampo
FROM estatusEmpleado E
 LEFT JOIN NOTIFICACION N ON E.Codigo = N.CODIGO
   WHERE 
        (N.USERN = 'ADMIN'
         OR N.USERN IS NULL)
     AND
        (N.ESTADO = 'N'
         OR N.ESTADO IS NULL);
    • 1