CODEWITHSUNDEEP
sqlsql-serverjoininner-join
PookPook
PookPook

Reputation: 467

SQL query inner join with 0 values

I have this table:

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

And have this table:

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

I wanted a table like this:

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

The query:

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

The result of this is query:

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

The problem with this is that query values ​​that are 0 don't appear.

Upvotes: 0

Views: 3238

Answers (3)

fthiella
fthiella

Reputation: 49049

I think you are looking for this:

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

this is not an INNER JOIN, but it's a cartesian join instead (every row of table1 is multiplied for every row of table2). I'm using SUM to count the rows where the INNER JOIN would have succeeded.

Upvotes: 3

Bulat
Bulat

Reputation: 6969

Try this:

(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

Upvotes: 1

SchmitzIT
SchmitzIT

Reputation: 9552

Try this:

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

Upvotes: 0

Related Questions