CODEWITHSUNDEEP
sqlsql-server-2008
lemunk
lemunk

Reputation: 2636

SQL: All records from one table, and all records from another, including null

Using SQL Server 2008.

Hi guys, I have a table called Language all I need from this table is LanguageID (1-10).
I have another table called UserQuiz and I need the ModuleID and a COUNT() of Passed.
The plan is to show 10 languages with 4 modules with a users passed count.

So it would be 40 records(LanguageIDs * ModuleIDs).
But not all languages have All Modules and thus don't have any records.

I need a query that will just fill in the missing module and put null in the record for usercount.

So far I've tried...

UPDATED:: 29/08/2013 @ 10:35 am (GMT).

CREATE TABLE #CrossTable(
LanguageID int,
ModuleID int
)
INSERT INTO #CrossTable
SELECT LanguageID, ModuleID 
FROM 
RoundupAcademy.dbo.Languages
CROSS JOIN
RoundupAcademy.dbo.CurrentModules
/*********************************************************************************/

/** get users via date and quiz **************************************************/
CREATE TABLE #userspassed(
userid int,
passed int,
moduleid int,
languageid int
)
INSERT INTO #userspassed
SELECT userprofile.UserId, passed, userquiz.moduleID, LanguageId
FROM 
UserProfile
LEFT JOIN 
UserQuiz
ON
UserProfile.UserId = UserQuiz.userID
WHERE
((Convert(datetime,LastLogin, 120) >= 
    Convert(datetime,@datefrom, 120) 
AND (Convert(datetime,LastLogin, 120) <= 
        convert(datetime,@datetoo, 120))))
AND
(passed is null or passed = 1)
/*********************************************************************************/

/**Get Modules per language count on users passed ********************************/
SELECT 
#CrossTable.languageID, 
#CrossTable.ModuleID,
coalesce(COUNT(#userspassed.userID),0) as users 
FROM 
#CrossTable
LEFT JOIN
#userspassed
ON 
#CrossTable.ModuleID = #userspassed.moduleID
GROUP BY #CrossTable.LanguageID, #CrossTable.ModuleID

/*********************************************************************************/

this does bring back 40 records but module "n" that is repeated 10 times for language also has repeated Users(count). seems there is only 4 values that is being applied to 10 of the languages for module 1 (value 94) and 10 for module 2 (value 89) and 10 for module 3 (value 104) and 10 for module 4 (value 28).

Each record should be different but it seems its applying the same values to all modules that are the same.

UPDATED:: 29/08/2013 @ 11:05 am (GMT).

I forgot to add

AND 
#CrossTable.LanguageID = #userspassed.languageid

seems to work now just going to check the values are correct

Upvotes: 2

Views: 248

Answers (1)

Stephen O&#39;Flynn
Stephen O&#39;Flynn

Reputation: 2329

Maybe link to the language on the JOIN as well. Also you shouldn't need a COALESCE on a COUNT:

SELECT 
    #CrossTable.languageID, 
    #CrossTable.ModuleID,
    COUNT(#userspassed.userID) as users 
FROM 
    #CrossTable
LEFT JOIN
    #userspassed ON 
      #CrossTable.LanguageID = #userspassed.languageid
      AND #CrossTable.ModuleeID = #userspassed.moduleid
GROUP BY #CrossTable.LanguageID, #CrossTable.ModuleID

Upvotes: 2

Related Questions