Inner join on boolean columns

I have a database where details of submitted documents on retirement application module (only relevant fields are shown) are stored. I'm trying to create a view where I get employee's personal information along with a table of boolean values representing current documents state. I have four tables:

1- Table for the "main records", this serves as an entry-point for the client side application

CREATE TABLE tbRegistro (
    ID int IDENTITY(0,1) PRIMARY KEY,
    ID_ANAGRAFICA int NOT NULL,
    ID_DOCUMENTAZIONE int,
    ID_COMUNICAZIONI int,
    UTENTE nvarchar(15) NOT NULL,
    DATA_CREAZIONE datetime NOT NULL,
    LOCK bit 
    );

Data example:

ID          ID_ANAGRAFICA ID_DOCUMENTAZIONE ID_COMUNICAZIONI 
----------- ------------- ----------------- ---------------- 
0           1             1                 0                
1           3             3                 1                
2           5             5                 2                
3           7             7                 3                

2- Table for the "Personal Informations" of the retiring employees

CREATE TABLE tbAnagrafica (
    ID int IDENTITY(0,1) PRIMARY KEY,
    MATRICOLA nvarchar(7) NOT NULL,
    COGNOME nvarchar(50) NOT NULL,
    NOME nvarchar(50) NOT NULL,
    COD_ENTE char(5) NOT NULL,
    NOME_ENTE nvarchar(150) NOT NULL,
    UTENTE nvarchar(15) NOT NULL,
    DATA_CREAZIONE datetime NOT NULL
    );

Data example:

ID          MATRICOLA COGNOME           NOME                                               
----------- --------- ----------------- ----------------
1           DRTVDR    SKYWALKER         ANAKIN
3           EMPR0R    PALPATINE         SHEEV
5           NRFHRD    SOLO              HAN
7           SCARRD    SOLO              BEN

3- Table that holds details of every single document submitted by the retiring aployee

CREATE TABLE tbDettaglioDoc(
    ID int IDENTITY(0,1) PRIMARY KEY,
    PROT_NUM int NOT NULL,
    PROT_DATA datetime NOT NULL,
    PRESENTE bit NOT NULL,
    INCOMPLETO bit NOT NULL,
    DISABILITATO bit NOT NULL,
    NOTE nvarchar(255),
    ID_TIPODOCUMENTO int NOT NULL,
    UTENTE nvarchar(15) NOT NULL,
    DATA_CREAZIONE datetime NOT NULL
    );

Data example:

ID          PROT_NUM    PROT_DATA               PRESENTE INCOMPLETO DISABILITATO ID_TIPODOCUMENTO 
----------- ----------- ----------------------- -------- ---------- ------------ ---------------- 
6           84528       2018-05-07 00:00:00.000 1        0          0            0               
7           84528       2018-05-07 00:00:00.000 1        0          0            3                
8           84528       2018-05-07 00:00:00.000 1        0          0            5               
11          84528       2018-05-07 00:00:00.000 1        0          0            9               
12          238785      2018-05-07 00:00:00.000 1        0          0            0               
13          238785      2018-05-07 00:00:00.000 1        0          0            3               
14          238785      2018-05-07 00:00:00.000 1        1          0            5               
25          51132       2018-05-07 00:00:00.000 1        0          0            4               
26          51132       2018-05-07 00:00:00.000 1        0          0            5               
28          51132       2018-05-07 00:00:00.000 1        0          0            9               
29          84350       2018-05-07 00:00:00.000 1        0          0            0               
30          84350       2018-05-07 00:00:00.000 1        1          0            3               

4- Table that holds all the documents' IDs for the application as a whole, storing NULL value when that particular document is still missing (just general names shown here):

CREATE TABLE tbDocumentazione(
    ID int IDENTITY(0,1) PRIMARY KEY,
    ID_ISTANZA int,
    ID_ATTIDISP int,
    ID_CEDOLINO int,
    ID_DATISENS int,
    ID_NULLAOSTA int,
    ID_INESDEBITI int,
    ID_DUM int,
    ID_MOD27 int,
    ID_CODFISC int,
    ID_DOCIDENTITA int, 
    ID_CERTMORTE int,
    ID_ATTONOTORIO int,
    UTENTE nvarchar(15) NOT NULL,
    DATA_CREAZIONE datetime NOT NULL
    );

Data example:

ID          ID_ISTANZA  ID_ATTIDISP ID_CEDOLINO ID_DATISENS ID_NULLAOSTA ID_INESDEBITI ID_DUM      ID_MOD27    ID_CODFISC  ID_DOCIDENTITA ID_CERTMORTE ID_ATTONOTORIO
----------- ----------- ----------- ----------- ----------- ------------ ------------- ----------- ----------- ----------- -------------- ------------ --------------
1           6           NULL        NULL        7           NULL         8             NULL        9           10          11             NULL         NULL          
3           12          NULL        NULL        13          NULL         14            NULL        NULL        15          16             NULL         NULL          
5           23          NULL        NULL        24          25           26            NULL        NULL        27          28             NULL         NULL          
7           29          NULL        NULL        30          NULL         31            NULL        32          33          34             NULL         NULL          
8           38          NULL        NULL        35          NULL         36            NULL        NULL        39          37             NULL         NULL          
9           40          NULL        NULL        41          NULL         42            NULL        43          44          45             NULL         NULL          

I would expected it to return something like this (nerd alert!)

  ID   COGNOME      NOME        MATRICOLA   ISTANZA   ATTI_DISP   CEDOLINO
+----+------------+-----------+-----------+---------+-----------+----------
  1    SKYWALKER    ANAKIN      DRTVDR      1         0           1
  2    PALPATINE    SHEEV       EMPROR      1         1           0
  3    SOLO         HAN         NRFHRD      0         1           1
  ...and so on

where "0= is shown when either the document hasn't been submitted or is incomplete, but I'm pretty new to T-SQL and every query I try fails. So I'm kinda stuck. Any help is REALLY appreciated.

Thanks in advance. Davide.

2 answers

  • answered 2018-05-21 22:41 Simon Wang

    OK I don't see the FK defined so I would go with assumption of ID_TIPODOCUMENTO in tbDettaglioDoc is linked to ID in tbAnagrafica, so a rough idea would be something as follow:

    SELECT P.ID, P.COGNOME, P.NOME, P.MATRICOLA,
    CASE WHEN COUNT(IDoc.ID) > 0 THEN 1 ELSE 0 END AS ISTANZA,
    CASE WHEN COUNT(ADoc.ID) > 0 THEN 1 ELSE 0 END AS ATTI_DISP,
    CASE WHEN COUNT(CDoc.ID) > 0 THEN 1 ELSE 0 END AS CEDOLINO
    FROM tbAnagrafica P
    LEFT JOIN tbDettaglioDoc I ON P.ID = I.ID_TIPODOCUMENTO AND I.PRESENTE = 1 AND I.INCOMPLETO = 0
    LEFT JOIN tbDocumentazione IDoc ON I.ID = IDoc.ID_ISTANZA
    
    LEFT JOIN tbDettaglioDoc A ON P.ID = A.ID_TIPODOCUMENTO AND A.PRESENTE = 1 AND A.INCOMPLETO = 0
    LEFT JOIN tbDocumentazione ADoc ON A.ID = ADoc.ID_ATTIDISP
    
    LEFT JOIN tbDettaglioDoc C ON P.ID = C.ID_TIPODOCUMENTO AND C.PRESENTE = 1 AND C.INCOMPLETO = 0
    LEFT JOIN tbDocumentazione CDoc ON C.ID = CDoc.ID_CEDOLINO
    GROUP BY P.ID, P.COGNOME, P.NOME, P.MATRICOLA
    

    I'm not saying it would just work but that should be the idea about how to do it in one way, depends on your table size and index it could be slow query.

    Another way is to create a temp/variable table with the structure of the desired result, you insert personal records into it first in one query, then update the table with three different queries each only caring about one type of document:

    Select P.ID, P.COGNOME, P.NOME, P.MATRICOLA, 0 AS ISTANZA, 0 AS ATTI_DISP, 0 AS CEDOLINO
    INTO #Result from tbAnagrafica P
    
    UPDATE R
    SET ISTANZA = 1
    FROM #Result R
        INNER JOIN tbDettaglioDoc I ON R.ID = I.ID_TIPODOCUMENTO
        INNER JOIN tbDocumentazione IDoc ON I.ID = IDoc.ID_ISTANZA
    WHERE I.PRESENTE = 1 AND I.INCOMPLETO = 0
    
    UPDATE R
    SET ATTI_DISP = 1
    ...
    

    And for the last part you can use EXISTS instead of the JOIN which ideally should be better on performance

    UPDATE R
    SET ISTANZA = 1
    FROM #Result R
    WHERE EXISTS(SELECT 1 FROM tbDettaglioDoc I
        INNER JOIN tbDocumentazione IDoc ON I.ID = IDoc.ID_ISTANZA
    WHERE R.ID = I.ID_TIPODOCUMENTO AND I.PRESENTE = 1 AND I.INCOMPLETO = 0)
    

  • answered 2018-05-22 10:12 Davide Vitali

    I've found a way to get what I wanted:

    SELECT
        tbRegistro.ID,
        tbAnagrafica.COGNOME, 
        tbAnagrafica.NOME,
        tbAnagrafica.MATRICOLA,
        (A.PRESENTE & ~A.INCOMPLETO) AS ISTANZA,
        (B.presente & ~B.incompleto) AS ATTI_DISP,
        (C.PRESENTE & ~C.INCOMPLETO) AS CEDOLINO,
        (D.PRESENTE & ~D.INCOMPLETO) AS DATISENS,
        (E.PRESENTE & ~E.INCOMPLETO) AS NULLAOSTA,
        (F.PRESENTE & ~F.INCOMPLETO) AS INESDEBITI,
        (G.PRESENTE & ~G.INCOMPLETO) AS DUM,
        (H.PRESENTE & ~H.INCOMPLETO) AS MOD27,
        (I.PRESENTE & ~I.INCOMPLETO) AS CODFISC,
        (J.PRESENTE & ~J.INCOMPLETO) AS DOCIDENTITA,
        (K.PRESENTE & ~K.INCOMPLETO) AS CERTMORTE,
        (L.PRESENTE & ~L.INCOMPLETO) AS ATTONOTORIO
    FROM
        tbRegistro
        INNER JOIN tbAnagrafica ON tbRegistro.ID_ANAGRAFICA = tbAnagrafica.ID
        INNER JOIN tbDocumentazione ON tbRegistro.ID_DOCUMENTAZIONE = tbDocumentazione.ID
        FULL JOIN tbDettaglioDoc AS A ON tbDocumentazione.ID_ISTANZA = A.ID
        FULL JOIN tbDettaglioDoc AS B ON tbDocumentazione.ID_ATTIDISP = B.ID
        FULL JOIN tbDettaglioDoc AS C ON tbDocumentazione.ID_CEDOLINO = C.ID
        FULL JOIN tbDettaglioDoc AS D ON tbDocumentazione.ID_DATISENS = D.ID
        FULL JOIN tbDettaglioDoc AS E ON tbDocumentazione.ID_NULLAOSTA = E.ID
        FULL JOIN tbDettaglioDoc AS F ON tbDocumentazione.ID_INESDEBITI = F.ID
        FULL JOIN tbDettaglioDoc AS G ON tbDocumentazione.ID_DUM = G.ID
        FULL JOIN tbDettaglioDoc AS H ON tbDocumentazione.ID_MOD27 = H.ID
        FULL JOIN tbDettaglioDoc AS I ON tbDocumentazione.ID_CODFISC = I.ID
        FULL JOIN tbDettaglioDoc AS J ON tbDocumentazione.ID_DOCIDENTITA = J.ID
        FULL JOIN tbDettaglioDoc AS K ON tbDocumentazione.ID_CERTMORTE = K.ID
        FULL JOIN tbDettaglioDoc AS L ON tbDocumentazione.ID_ATTONOTORIO = L.ID
    WHERE tbregistro.ID IS NOT NULL
    

    Example on results:

    ID          COGNOME             NOME        MATRICOLA ISTANZA ATTI_DISP CEDOLINO DATISENS NULLAOSTA INESDEBITI DUM   MOD27 CODFISC DOCIDENTITA CERTMORTE ATTONOTORIO
    ----------- ------------------- ----------- --------- ------- --------- -------- -------- --------- ---------- ----- ----- ------- ----------- --------- -----------
    0           PALPATINE           SHEEV       EMPR0R     1       0         1        1        1         1          NULL  NULL  NULL    1           NULL      NULL
    1           SKYWALKER           ANAKIN      DRTVDR     1       NULL      NULL     1        NULL      NULL       0     NULL  NULL    1           NULL      NULL
    

    Where 1 means correctly submitted, 0 submitted with errors, NULL not submitted