Query help show all from table

[ayh1]

Hi

I have a query:

SELECT     TOP (100) PERCENT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, gp.OrganisationCode, gp.Name, 
                      gp.Address1, gp.Address2, SUM(CASE WHEN a.AttendanceStatus IN (9) THEN 1 ELSE 0 END) AS Attended, SUM(CASE WHEN a.AttendanceStatus IN (3) 
                      THEN 1 ELSE 0 END) AS [Did not attend], SUM(CASE WHEN a.AttendanceStatus IN (7) THEN 1 ELSE 0 END) AS Cancelled, gp.CCGRegion
FROM         dbo.tblGP_Practices AS gp INNER JOIN
                      dbo.tblGP_PatientLink AS pl ON gp.GPPracticeID = pl.GPPracticeID INNER JOIN
                      dbo.tblPatient AS p ON pl.PatientID = p.PatientID INNER JOIN
                      dbo.tblAppointments AS a ON p.PatientID = a.PatientID INNER JOIN
                      dbo.tblCourses ON a.CourseID = dbo.tblCourses.Course_ID
WHERE     (a.AttendanceStatus IN (1, 2, 3, 4, 6, 7, 8, 9, 10)) AND (a.AttendanceStatus IS NOT NULL)
GROUP BY gp.OrganisationCode, gp.Name, gp.Address1, gp.Address2, gp.CCGRegion, YEAR(dbo.tblCourses.CourseDate), CONVERT(char(3), dbo.tblCourses.CourseDate, 0)
HAVING      (gp.CCGRegion = N'city ccg')
ORDER BY gp.OrganisationCode

this returns the correct results but I want to be able to see all OrganisationCode from table gp (so basically all gp practices) even if they have 0 Attended, DNA or cancelled. the current query just limits it down and shows be only those with a CourseDate and value for Attended, Did not attend or cancelled.

any help would be appreciated

thanks

 

[CJ_London]

convert your joins to left joins between table gp and the other tables (in the query builder, double click on the join and select the option 'Include ALL records from table gp and only those records...')

 

[ayh1]

convert your joins to left joins between table gp and the other tables (in the query builder, double click on the join and select the option 'Include ALL records from table gp and only those records...')

When i select all rows from gp (right clicking on the link/join) it creates a right outer join but still the same results i tried changing all to right outer and no luck, then changed all to left outer and no luck

 

[ayh1]

convert your joins to left joins between table gp and the other tables (in the query builder, double click on the join and select the option 'Include ALL records from table gp and only those records...')

I tried

SELECT     TOP (100) PERCENT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, gp.OrganisationCode, gp.Name, 
                      gp.Address1, gp.Address2, SUM(CASE WHEN a.AttendanceStatus IN (9) THEN 1 ELSE 0 END) AS Attended, SUM(CASE WHEN a.AttendanceStatus IN (3) 
                      THEN 1 ELSE 0 END) AS [Did not attend], SUM(CASE WHEN a.AttendanceStatus IN (7) THEN 1 ELSE 0 END) AS Cancelled, gp.CCGRegion
FROM         dbo.tblPatient AS p LEFT OUTER JOIN
                      dbo.tblGP_PatientLink AS pl ON p.PatientID = pl.PatientID RIGHT OUTER JOIN
                      dbo.tblAppointments AS a ON p.PatientID = a.PatientID RIGHT OUTER JOIN
                      dbo.tblCourses ON a.CourseID = dbo.tblCourses.Course_ID FULL OUTER JOIN
                      dbo.tblGP_Practices AS gp ON pl.GPPracticeID = gp.GPPracticeID
WHERE     (a.AttendanceStatus IN (1, 2, 3, 4, 6, 7, 8, 9, 10)) OR
                      (a.AttendanceStatus IS NULL)
GROUP BY gp.OrganisationCode, gp.Name, gp.Address1, gp.Address2, gp.CCGRegion, YEAR(dbo.tblCourses.CourseDate), CONVERT(char(3), dbo.tblCourses.CourseDate, 0)
HAVING      (gp.CCGRegion = N'city ccg') AND (CONVERT(char(3), dbo.tblCourses.CourseDate, 0) = 'apr') OR
                      (gp.CCGRegion = N'city ccg') AND (CONVERT(char(3), dbo.tblCourses.CourseDate, 0) IS NULL)
ORDER BY gp.OrganisationCode

it returns more gp practices i now get 57 instead of 35 which is better but it should be returning 62

 

[ayh1]

convert your joins to left joins between table gp and the other tables (in the query builder, double click on the join and select the option 'Include ALL records from table gp and only those records...')

I have tried

SELECT     TOP (100) PERCENT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, gp.OrganisationCode, gp.Name, 
                      gp.Address1, gp.Address2, SUM(CASE WHEN a.AttendanceStatus IN (9) THEN 1 ELSE 0 END) AS Attended, SUM(CASE WHEN a.AttendanceStatus IN (3) 
                      THEN 1 ELSE 0 END) AS [Did not attend], SUM(CASE WHEN a.AttendanceStatus IN (7) THEN 1 ELSE 0 END) AS Cancelled, gp.CCGRegion
FROM         dbo.tblGP_Practices AS gp LEFT OUTER JOIN
                      dbo.tblGP_PatientLink AS pl LEFT OUTER JOIN
                      dbo.tblPatient AS p ON pl.PatientID = p.PatientID LEFT OUTER JOIN
                      dbo.tblAppointments AS a ON p.PatientID = a.PatientID ON gp.GPPracticeID = pl.GPPracticeID LEFT OUTER JOIN
                      dbo.tblCourses ON a.CourseID = dbo.tblCourses.Course_ID
WHERE     (a.AttendanceStatus IN (1, 2, 3, 4, 6, 7, 8, 9, 10)) OR
                      (a.AttendanceStatus IS NULL)
GROUP BY gp.OrganisationCode, gp.Name, gp.Address1, gp.Address2, gp.CCGRegion, YEAR(dbo.tblCourses.CourseDate), CONVERT(char(3), dbo.tblCourses.CourseDate, 0)
HAVING      (gp.CCGRegion = N'city ccg') AND (CONVERT(char(3), dbo.tblCourses.CourseDate, 0) = 'apr') OR
                      (gp.CCGRegion = N'city ccg') AND (CONVERT(char(3), dbo.tblCourses.CourseDate, 0) IS NULL)
ORDER BY gp.OrganisationCode

I get 97 records but I have some rows as duplicate for example the gp below is repeated as it is linked to a coursedate and has values for attended did not attend and cancelled so reurns one row by default (left join to ow all gps) and one row because of the date and values

NULLNULLC82002COUNTESTHORPE HEALTH CENTRECOUNTESTHORPE HEALTH CTR.CENTRAL STREET000

Apr2015C82002COUNTESTHORPE HEALTH CENTRECOUNTESTHORPE HEALTH CTR.CENTRAL STREET101

how can i say if there is a value in attended did not attend or cancelled then do not show the other row?

 

[CJ_London]

not sure if it makes a real difference, but I avoid mixing left and right joins

to convert a right join to a left join, delete the join and recreate by drawing from the table you want to be the 'left'

However you seem to have gone overboard with the left and right joins.

You are using dbo. so I presume the data is in SQL Server?

I can't actually 'visualise' your query but suspect you need to create one or more subqueries combining some of the tables together on inner joins, then left join to that subquery