We have two tables one with ids and one with names. Some id's are not required (are nullable) for example :
rowId item1Id, item2Id, item3Id (item3Id can be null)
After we join this two tables we get id's and names. But if item3 Id is null that row is not shown.
rowId, item1Id, item2Id, item3Id
1, 2, 3, 4
2, 1, 5, NULL
In our case we get as result one row ( 1 row)
So if it's null than it doesnt exists in Table2 and the row is not shown, but we would like to display null. So when we left join these two tables the result should have 2 rows.
This is the sql :
SELECT Recipes.recipeId, Recipes.userId,Users.firstName + ' ' + Users.lastName as userName, servingTypeId, Codings.coding as servingType, categoryId, Codings_2.coding as healthAspects, continentId, Codings_3.coding as continent, countryId, CodingsAssociated.coding as country, typeOfPreparationId, Codings_4.coding as typeOfPreparation, flavourId, Codings_5.coding as flavour, preparationSkillId, Codings_6.coding as preparationSkill, seasonId, activePreparationTime, overallPreparationTime, isLocalDelight, servings, Codings_7.coding as season , calories, youTubeId, datePosted, isComposite, Recipes.isApproved, Recipes.timestamp, title, localDelightRegion, otherFeatures, cookingInstructions FROM Recipes LEFT OUTER JOIN Codings ON Recipes.servingTypeId = Codings.codingKeyId LEFT OUTER JOIN Codings as Codings_2 ON Recipes.categoryId = Codings_2.codingKeyId LEFT OUTER JOIN Codings as Codings_3 ON Recipes.continentId = Codings_3.codingKeyId LEFT OUTER JOIN CodingsAssociated ON Recipes.countryId = CodingsAssociated.codingKeyId LEFT OUTER JOIN Codings as Codings_4 ON Recipes.typeOfPreparationId = Codings_4.codingKeyId LEFT OUTER JOIN Codings as Codings_5 ON Recipes.flavourId = Codings_5.codingKeyId LEFT OUTER JOIN Codings as Codings_6 ON Recipes.preparationSkillId = Codings_6.codingKeyId LEFT OUTER JOIN Codings as Codings_7 ON Recipes.seasonId = Codings_7.codingKeyId LEFT OUTER JOIN RecipesTranslations ON Recipes.recipeId = RecipesTranslations.recipeId LEFT OUTER JOIN Users ON Recipes.userId = Users.userId WHERE CodingsAssociated.languageId = @languageId AND Codings.languageId = @languageId AND Codings_2.languageId = @languageId AND Codings_3.languageId = @languageId AND Codings_4.languageId = @languageId AND Codings_5.languageId = @languageId AND Codings_6.languageId = @languageId AND RecipesTranslations.languageId = @languageId AND Codings_7.languageId = @languageId
For example because seasonId is sometimes null the join for Codings_7.coding is not shown, but I won`t to show also these rows where ids are null that the name also be null.