sql query

[hlsc1983]

i need help on what sql query to use to populate a list view:

I have the following tables:
1)Subjects
2)Departments
3)Semesters
4)SubjectsDepartments junction table ( since there exists a many to many relationship between Subjects and Departments
5)SubjectsSemesters junction table( since there exists a many to many relationship between Subjects and Semesters)




In vb.net front end, there are two combo boxes and one listview. First combo box is to select the semester and a second combo box to select the department. Using the values in these two combo boxes, i want to populate a listview with subject names. I know how to populate the comboboxes but i dont know how to write the sql query to get the subject names as it involves many tables. please help.

 

[IanRyder]

Hi,

So long as you have setup your ComboBox's correctly so that you can easily access the semester_id and department_id properties of your DataTables then you can get your course information with the following SQL Query:-

SELECT Subjects.subject_id, Subjects.subject_name
FROM Subjects 
INNER JOIN Departments_Subjects_Junction ON Subjects.subject_id = Departments_Subjects_Junction.subject_id
INNER JOIN Semesters_Subjects_Junction ON Subjects.subject_id = Semesters_Subjects_Junction.subject_id
WHERE Departments_Subjects_Junction.department_id = @DepartmentID AND Semesters_Subjects_Junction.semester_id = @SemesterID

Hope that helps.

Cheers,

Ian

 

[hlsc1983]

Hi,

So long as you have setup your ComboBox's correctly so that you can easily access the semester_id and department_id properties of your DataTables then you can get your course information with the following SQL Query:-

SELECT Subjects.subject_id, Subjects.subject_name
FROM Subjects 
INNER JOIN Departments_Subjects_Junction ON Subjects.subject_id = Departments_Subjects_Junction.subject_id
INNER JOIN Semesters_Subjects_Junction ON Subjects.subject_id = Semesters_Subjects_Junction.subject_id
WHERE Departments_Subjects_Junction.department_id = @DepartmentID AND Semesters_Subjects_Junction.semester_id = @SemesterID

Hope that helps.

Cheers,

Ian

k the query seems to be working fine now but another error has come up :

"Conversion failed when converting the nvarchar value 'department_id' to data type int."

I am confused because 'department_id' is stored as int in the database.

 

[hlsc1983]

i got it . it should have been 'departmentCb.selectedvalue' and not 'departmentCb.valuemember'. Similarly for semesterCb.selectedvalue