Problem when joining SQL tables

Question

Problem when joining SQL tables

Navigation

#1 by (1 votes)

1

I have a query:

SELECT p.id, 
       resultado, 
       (count(resultado)*100)/(SELECT COUNT(*) FROM IAssesment_Evaluaciones WHERE area='TlP/LOP2.1') count 
    FROM IAssesment_Evaluaciones e 
    INNER JOIN IAssesment_Resultados r 
       ON e.id=r.id_evaluacion 
    LEFT OUTER JOIN IAssesment_Tipo_Resultados t 
       ON t.id=r.resultado 
    INNER JOIN IAssesment_Preguntas p 
       ON r.id_pregunta=p.id 
    WHERE area='TlP/LOP2.1' 
    GROUP BY resultado,p.id ORDER BY p.id,resultado

This shows the data well:

id  resultado   count
1   2           100
2   2           100
3   2           50
3   3           50
4   1           33
4   2           66
5   1           50
5   2           33
5   3           16

My problem is that the IAssesment_Tipo_Resultados table has 3 types of results (1,2,3). What I need is for my query to return the 3 types of values even though it has no value. As an example of the results of the previous query, this is the result I need:

id    resultado    COUNT
1    2             100
1    1             0
1    3             0

Thanks for your help

sql-server

asked by pepe arzate 09.04.2018 в 16:45

source

1 answer

Open modality of confirmation before sending Android Does Sharedpreferences save null?

score 1 · Answer 1

Table IAssesment_Tipo_Resultado has other types of results but they were not selected because they do not generate a match in the JOIN

Use a combination of ISNULL Y FULL OUTER JOIN

SELECT p.id, 
       ISNULL(resultado,t.Id) as resultado, 
       ISNULL((count(resultado)*100)/(SELECT COUNT(*) FROM 
       IAssesment_Evaluaciones WHERE area='TlP/LOP2.1'),0) AS COUNT
    FROM IAssesment_Evaluaciones e 
    INNER JOIN IAssesment_Resultados r 
       ON e.id=r.id_evaluacion 
    FULL OUTER JOIN IAssesment_Tipo_Resultados t 
       ON t.id=r.resultado 
    INNER JOIN IAssesment_Preguntas p 
       ON r.id_pregunta=p.id 
    WHERE area='TlP/LOP2.1' 
    GROUP BY ISNULL(resultado,t.Id),p.id ORDER BY p.id,ISNULL(resultado,t.Id)