Page 1 of 1
Many to many SQL
Posted: Mon 06 Aug 2012 09:20
by 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 ?
Re: Many to many SQL
Posted: Mon 06 Aug 2012 12:40
by 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'
Re: Many to many SQL
Posted: Thu 09 Aug 2012 05:04
by 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?
Re: Many to many SQL
Posted: Thu 09 Aug 2012 09:42
by 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.