SQL-Abfrage innere Verknüpfung mit 0 Werten

Ich habe diesen Tisch:

idSection | idQuestion | title | enunciation | idScale 1 | 1 | title 1 | question 1 | 3 1 | 1 | title 1 | question 1 | 3 1 | 1 | title 1 | question 1 | 3 1 | 1 | title 1 | question 1 | 2 1 | 1 | title 1 | question 1 | 5 1 | 2 | title 2 | question 2 | 1 1 | 2 | title 2 | question 2 | 3 1 | 3 | title 3 | question 3 | 1 

Und hab diesen Tisch:

 idScale | name 1 | Very Bad 2 | Bad 3 | Good 4 | Very Good 5 | Excellent 

Ich wollte einen Tisch wie folgt:

 idSection | idQuestion | title | enunciation | Total | Name 1 | 1 | title 1 | question 1 | 0 | Very Bad 1 | 1 | title 1 | question 1 | 0 | Bad 1 | 1 | title 1 | question 1 | 3 | Good 1 | 1 | title 1 | question 1 | 0 | Very Good 1 | 1 | title 1 | question 1 | 1 | Excellent 1 | 2 | title 2 | question 2 | 0 | Very Bad 1 | 2 | title 2 | question 2 | 1 | Bad 1 | 2 | title 2 | question 2 | 3 | Good 1 | 2 | title 2 | question 2 | 0 | Very Good 1 | 2 | title 2 | question 2 | 0 | Excellent 

Die Abfrage:

 SELECT t1.idSection, t1.idQuestion, t1.title, t1.enunciation, COUNT(t1.idScale) as Total, t2.name FROM table1 AS t1 INNER JOIN table2 as t2 ON t2.idScale = t1.idScale GROUP BY t1.idSection, t1.idQuestion, t1.title, t1.enunciation, t2.name 

Das Ergebnis ist Abfrage:

 idSection | idQuestion | title | enunciation | Total | Name 1 | 1 | title 1 | question 1 | 3 | Good 1 | 1 | title 1 | question 1 | 1 | Excellent 1 | 2 | title 2 | question 2 | 1 | Bad 1 | 2 | title 2 | question 2 | 3 | Good 

Das Problem damit ist, dass Abfragewerte, die 0 sind, nicht angezeigt werden.

Ich denke, du suchst das nach:

 SELECT t1.idSection, t1.idQuestion, t1.title, t1.enunciation, SUM(case when t1.idScale=t2.idScale then 1 else 0 end) as Total, t2.name FROM table1 AS t1, table2 as t2 GROUP BY t1.idSection, t1.idQuestion, t1.title, t1.enunciation, t2.name, t2.idScale ORDER BY t1.idSection, t1.idQuestion, t2.idScale 

Dies ist kein INNER JOIN, aber es ist ein kartesischer Join statt (jede Zeile von table1 wird für jede Zeile von table2 multipliziert). Ich benutze SUM, um die Zeilen zu zählen, wo die INNER JOIN gelungen wäre.

Versuche dies:

 (SELECT t1.idSection, t1.idQuestion, t1.title, t1.enunciation, COUNT(t1.idScale) as Total, t2.name FROM table1 t1 JOIN table2 t2 ON t2.idScale=t1.idScale GROUP BY t1.idSection, t1.idQuestion, t1.title, t1.enunciation, t2.name) UNION (SELECT DISTINCT t1.idSection, idQuestion, title, enunciation, 0 as Total , t2.name FROM table1 t1,table2 t2 WHERE NOT EXISTS (SELECT * FROM table1 JOIN table2 ON table2.idScale=table1.idScale WHERE t1.idSection = table1.idSection AND t1.idQuestion = table1.idQuestion AND t2.idScale= table2.idScale) ) 

http://sqlfiddle.com/#!3/7332c/15

Versuche dies:

 SELECT t1.idSection, t1.idQuestion, t1.title, t1.enunciation, COUNT(t1.idScale) as Total, t2.name FROM table1 AS t1 RIGHT JOIN table2 as t2 ON t2.idScale=t1.idScale GROUP BY t1.idSection, t1.idQuestion, t1.title, t1.enunciation, t2.name