Try to search your question here, if you can't find : Ask Any Question Now ?

Query using three tables, joining 2, counting records where 2 columns match from 3rd?

HomeCategory: stackoverflowQuery using three tables, joining 2, counting records where 2 columns match from 3rd?
Avatarjulie asked 3 months ago

I need to find which course offerings are currently full (the number of students enrolled equals the
capacity of the room it is held in)? For each of the course offerings in the results, list its
symbol, number, section number, building, room number, and capacity.

I know the three tables I need to use are these enrolled, courseoffering_of, and location with these inserts:

INSERT INTO enrolled (netID, symbol, number, sectionNumber) VALUES
('aec3', 'CSE', '4503', '01'),
('hc23', 'CSE', '1384', '02'),
('jjc13', 'CSE', '1384', '02'),
('wp4', 'CSE', '1384', '02' );

INSERT INTO courseoffering_of (symbol, `number`, building, room, 
sectionNumber) VALUES
('CSE', '4503', 'Butler', '100', '01'),
('CSE', '1384', 'Butler', '103', '01'),
('CSE', '1384', 'Butler', '103', '02'),
('ECE', '4713', 'Old Main', '2830', '01');

INSERT INTO location (building, room, capacity) VALUES
('Butler', '100', 5),
('Butler', '102', 2),
('Butler', '103', 2),
('Old Main', '2830', 6),
('Old Main', '3030', 3);`

I came up with some pseudocode to help me solve it:

1. merge courseoffering_of and location tables
2. count students in each class from enrolled (same number and section number)
3. select where class from enrolled = class from merged table (same number 
and section number)
4. compare number of students counted in that class with room capacity
5. if students enrolled = room capacity return the entry from merged table

I have no idea what to do for this query. I originally thought creating a new table from courseoffering_of and location would help, but our teacher wants us to do this all in a single query. I tried an inner join but couldn’t figure out how to use it in this situation.

Any help would be greatly appreciated, thank you.

1 Answers
Best Answer
AvatarArben answered 3 months ago
Your Answer

18 + 0 =

Popular Tags

WP Facebook Auto Publish Powered By : XYZScripts.com