Hello, I am trying to create an SQL query to join some tables but I can't get it right so I'm hoping I can get some help here-it's for a college assignment and the deadline is looming.

I've got two tables-one with student details (name,address etc etc) and another one containing the information on the subjects that each one is taking.It is only composed of two fields-studentID which is the foreign key to the first table and another called subject which shows a subject taken by the student.

Natually one student can take several subjects so a slice of the table would be something like

studentid subject
1 history
1 geography
1 art
2 maths
2 history
2 geography
3 physics
3 history
etc etc

What I want to do is have a query where one can see the names of the students(in the studentDetails table) that take a particular combination of subjects.For example I would like a query that could tell me which students take BOTH history and geography.(1 & 2 but not 3 in the example above). I realise that this must be a relatively simple query but I don't have much experience with SQL queries.

Any help would be greatly appreciated

Thanks.

You would typically want to have a Subjects table with numeric keys like your Students table. Your Subjects table is not in normal form.

I suggest that you use three tables like so:

Students:
StudentID (int)
StudentName (VarChar(100))

StudentSubject:
StudentID (int)
SubjectID (int)

Subject:
SubjectID (int)
SubjectName (VarChar(100))

Your query to return all combinations would look something like this:
select StudentName, SubjectName
from Students inner join StudentSubject on Students.StudentID = StudentSubject.StudentID
inner join Subjects on StudentSubject.SubjectID = Subject.SubjectID

Here's a complete query to create and return rows from these tables:

-- run if tables exist:
drop table Students
drop table Subjects
drop table StudentSubject
go

create table Students (StudentID int not null identity, StudentName VarChar(100) not null)
create table Subjects (SubjectID int not null identity, SubjectName VarChar(100) not null)
create table StudentSubject (StudentID int not null, SubjectID int not null)

insert into Students (StudentName) select 'Fred'
insert into Students (StudentName) select 'Barney'
insert into Students (StudentName) select 'Wilma'
insert into Students (StudentName) select 'Betty'

insert into Subjects (SubjectName) select 'Nuclear Physics'
insert into Subjects (SubjectName) select 'History'
insert into Subjects (SubjectName) select 'Basket Weaving'
insert into Subjects (SubjectName) select 'Geography'

insert into StudentSubject (StudentID, SubjectID) select 1, 1
insert into StudentSubject (StudentID, SubjectID) select 1, 2
insert into StudentSubject (StudentID, SubjectID) select 2, 3
insert into StudentSubject (StudentID, SubjectID) select 2, 4
insert into StudentSubject (StudentID, SubjectID) select 3, 1
insert into StudentSubject (StudentID, SubjectID) select 3, 2
insert into StudentSubject (StudentID, SubjectID) select 3, 4
insert into StudentSubject (StudentID, SubjectID) select 4, 2
insert into StudentSubject (StudentID, SubjectID) select 4, 3
insert into StudentSubject (StudentID, SubjectID) select 4, 4

select * from Students
select * from Subjects
select * from StudentSubject

select StudentName from
(
select Students.StudentID, StudentName, Subjects.SubjectID, SubjectName
from Students inner join StudentSubject on Students.StudentID = StudentSubject.StudentID
inner join Subjects on StudentSubject.SubjectID = Subjects.SubjectID
where Subjects.SubjectID IN (2, 4)
) as ss
group by ss.StudentID, ss.StudentName
having Count(ss.SubjectID) = 2

------------------------------------------------

Something to think about:
In an enrollment system, students take classes, not subjects. This means that ideally you would have a Classes table containing a SubjectID foreign key and the bridge table would be StudentClass rather than StudentSubject.

Have fun





select

Paul Turley, MCSD, MCDBA, MCT, MSF Practitioner