Can someone please help with an SQL Query I am facing.
Currently I am using Visual Studio 2013 Query Designer for my SSRS reports and I'm trying to select Students that are enrolled at their current School. However when testing my query for a selected student, it is returning all possible schools and saying the student has attended all 317 of them which is incorrect. My question is how do I find the school that a student has attended/attending?
The query code is below:
SELECT Person.Firstname, Person.Surname, Company.Name AS School, Company.CompanyCategory, Student.StudentNumber
FROM Student INNER JOIN
Person ON Student.ID = Person.ID CROSS JOIN
Company
WHERE (Company.CompanyCategory = 'Delivery Location') AND (Student.StudentNumber = '....')
As Sandeep mentioned, you are cross joining to Company (I'm assuming this is school?). You'll probably have to INNER JOIN to Company and specify a JOIN clause. The table Student might have CompanyID or something? You can JOIN that on the Company.ID.
So the query becomes something like this:
SELECT Person.Firstname, Person.Surname, Company.Name AS School, Company.CompanyCategory, Student.StudentNumber
FROM Student INNER JOIN
Person ON Student.ID = Person.ID INNER JOIN
--Not sure if CompanyID is the right field
Company ON Student.CompanyID = Company.ID
WHERE (Company.CompanyCategory = 'Delivery Location') AND (Student.StudentNumber = '....')