sql query

hlsc1983

Member
Joined
Mar 6, 2014
Messages
8
Programming Experience
1-3
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)


1.png

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.

2.png
 

IanRyder

Well-known member
Joined
Sep 9, 2012
Messages
1,130
Location
Healing, NE Lincs, UK
Programming Experience
10+
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
 
Last edited:

hlsc1983

Member
Joined
Mar 6, 2014
Messages
8
Programming Experience
1-3
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

Member
Joined
Mar 6, 2014
Messages
8
Programming Experience
1-3
i got it . it should have been 'departmentCb.selectedvalue' and not 'departmentCb.valuemember'. Similarly for semesterCb.selectedvalue
 
Top Bottom