How to use one table multiple times for inner join in SQL Server?
I am trying to make a table that includes join between 3 tables in the MSSS 2008. There is a fact table, a date table, and a course table. I should join them to make a base table. In date table there is a one parameter that name is Academic Year lookup, and the values in this parameter is like 2000/1, 2001/2. This parameter in the base table should separate to three parameter such as CensusYear, StartYear, and ApplicationYear. Therefore, I need the data table multiple times. I executed a inner join query, and already I have four inner join statement, but I am getting some extra years, and I'm losing some years. I believe, my query should be wrong somewhere. The attached file is include the design view that created in the MS Access, it'll help to see the tables, and understand what I need to create. [design view that created in the MS Access] [Query that excuted to create a table in MSSS 2008] : /storage/temp/3820-design-view-in-ms-access.docx : /storage/temp/3821-basetable.txt
In your table Student.FactApplicationSnapshot do you have any NULL values in the columns CensusDateSK, AcademicYearStartDateSK, ApplicationDateSK or CourseSK? If so you might be inadvertently knocking out rows you want to see because of your INNER JOINs. If you LEFT JOIN from Student.FactApplicationSnapshot to any of those tables in which NULLs might come into play, you might have better luck. Be careful though to alter your WHERE clause as well though, as it will have the effect of turning the LEFT JOINs back into INNER JOINs where it refers to tables being joined to. SELECT appl.EventCount ,cens.AcademicYearLookup AS CensusYear ,acyr.AcademicYearLookup AS StartYear ,apdt.AcademicYearLookup AS ApplicationYear ,cens.CurrentWeekComparisonFlag ,cens.AcademicWeekOfYear ,CASE WHEN appl.ApplicationCensusSK = 1 THEN 'Same Year' WHEN appl.ApplicationCensusSK = 2 THEN 'Next Year' WHEN appl.ApplicationCensusSK = 5 THEN 'Last Year' ELSE 'Other' END AS CensusYearDescription ,cens.CurrentAcademicYear ,appl.StudentCodeBK ,appl.ApplicationSequenceNoBK ,appl.CourseSK ,appl.CourseGroupSK ,appl.CourseMoaSK ,appl.CboSK ,appl.CourseTaughtAbroadSK ,appl.ApplicationStatusSK ,appl.ApplicationFeeStatusSK ,appl.DecisionResponseSK ,appl.NationalityCountrySK ,appl.DomicileCountrySK ,appl.TargetRegionSK ,appl.InternationalSponsorSK INTO dbo.BaseTable3yrs FROM Student.FactApplicationSnapshot AS appl LEFT JOIN Conformed.DimDate AS cens ON appl.CensusDateSK = cens.DateSK AND cens.CurrentAcademicYear In (0,-1) LEFT JOIN Conformed.DimDate AS acyr ON appl.AcademicYearStartDateSK = acyr.DateSK LEFT JOIN Conformed.DimDate AS apdt ON appl.ApplicationDateSK = apdt.DateSK LEFT JOIN Student.DimCourse cors ON appl.CourseSK = cors.CourseSK AND cors.DepartmentShortName = 'TEACH ED' WHERE appl.ApplicationCensusSK In (1,2,5) ;