Post reply

query help display all not only limit

  • January 28, 2015 at 8:17 am

    #300650

    Hi I have a query:

    SELECT COUNT(dbo.tblAppointments.PatientID) AS NumberOfPatientsCurrentlyEnrolled, dbo.tblLkup_Location.LocationDesc, dbo.tblCourses.CourseDate,dbo.tblLkup_AppTypes.AppDescription, dbo.tblCourses.CourseType FROM dbo.tblCourses INNER JOIN dbo.tblLkup_Location ON dbo.tblCourses.Location_ID = dbo.tblLkup_Location.LocationID LEFT OUTER JOIN dbo.tblAppointments ON dbo.tblCourses.Course_ID = dbo.tblAppointments.CourseID INNER JOIN dbo.tblLkup_AppTypes ON dbo.tblCourses.CourseType = dbo.tblLkup_AppTypes.TypeID WHERE(NOT (dbo.tblAppointments.AttendanceStatus = 7))GROUP BY dbo.tblLkup_Location.LocationDesc, dbo.tblCourses.CourseDate, dbo.tblLkup_AppTypes.AppDescription, dbo.tblCourses.CourseType

    there are 54 courses stored in the table, attendance status 7 means cancelled. The query only returns 4 courses that have been booked on, how can I modify this query to display all courses regardless if a patient has been enrolled or not?

    thanks

  • January 28, 2015 at 8:36 am

    #1773710

    You can use left/right join based on the tables.

  • January 28, 2015 at 9:01 am

    #1773717

    If you use a column in the where clause from a table joined by an outer join, your converting it to an inner join. Check the following alternative:

    SELECT COUNT(a.PatientID) AS NumberOfPatientsCurrentlyEnrolled,

    l.LocationDesc,

    c.CourseDate,

    t.AppDescription,

    c.CourseType

    FROM dbo.tblCourses c

    INNER JOIN dbo.tblLkup_Location l ON c.Location_ID = l.LocationID

    LEFT OUTER JOIN dbo.tblAppointments a ON c.Course_ID = a.CourseID AND a.AttendanceStatus <> 7

    INNER JOIN dbo.tblLkup_AppTypes t ON c.CourseType = t.TypeID

    GROUP BY l.LocationDesc,

    c.CourseDate,

    t.AppDescription,

    c.CourseType

    Using 3-part or 4-part names in the column list is deprecated and I suggest you to stop using it to prevent future errors. Alias make easier to read as long as they work as reference to the table.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 28, 2015 at 9:03 am

    #1773722

    THANKS

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply