Many to many SQL

[marsheng]

I'm using Delphi and I assume I can enter what I need as an SQL query but I'm not sure how to do it.

EG School subjects, teachers and students. How do I find the students who take a subject XXXX if I know the students in the teachers classes who take that subject, and I know what subjects the teachers teaches and there can be more than one teacher per subject. ?

Eg List all the students who History.

Query - Find all the History teachers and then find all the Students who belong to those Teachers.

How do I do this ?

[AndreyZ]

Hello,

Assuming you have the following tables:

Code: Select all

students:
studentid, studentname

teachers:
teacherid, teachername

subjects:
subjectsid, subjectname

teachers_students
id, teacherid, studentid

teachers_subjects:
id, teacher_id, subject_id

, you can use the query like this:

Code: Select all

select st.studentname from students st, teachers te, subjects su, teachers_students tst, teachers_subjects tsu
where
st.studentid = tst.studentid and te.teacherid = tst.teacherid and
te.teacherid = tsu.teacher_id and tsu.subject_id = su.subjectsid and
su.subjectname = 'History'

[marsheng]

Thanks for the reply Andry. I'm taking a few days off to learn SQL. I have never had to use any SQL with Delphi before.

What are the pros and cons of using a DB with a relationship against a MasterSource MasterFeild created in Delphi?

[AndreyZ]

It is really up to you to decide where you want to implement the logic of the linked data. Using database relationships, the server takes care about correct deleting and updating of records in the linked tables. The server is responsible for keeping data consistent. Using master-detail relationships set on a client, you have to take care of the same things. In this case, you have the full control of the data flow, so you can implement the desired behaviour that differs from the one the server has.