CODEWITHSUNDEEP
phpmysqljoinmapping
Sean
Sean

Reputation: 1098

mysql distinct query using joins

I have a complex database relationship (to me its complex). In theory, I think it a good design, but my roadblock now is getting data out of it in as few queries as possible. Here is the database structure I have:

student table: some fields like name, phone, email, etc.

students_requirements table (mapping table): student_id, requirement_id, date

requirements table (belongs to a requirement type): id, requirement_type_id, name

requirement_type table (has many requirements): id, type, name,

Ok, so here is an example of how it is used. I can build requirement types. An example would be something like an assignment. Each assignment has multiple requirements. A student can pass off requirements for a specific assignment, but doesn't necessarily have requirements passed off for all assignments. So I would want to query all assignments by student. So say there are 50 assignments entered in the system, and jon smith has entered requirements for 4 of those assignments. I would like to query by jon smith id to find all assignments that he has entered any requirements for.

I hope that makes sense. My only guess is to use a join, but to be honest, I really don't understand them very well.

Any help would be awesome!

Upvotes: 3

Views: 117

Answers (1)

Bobulous
Bobulous

Reputation: 13169

Try this:

SELECT * FROM student_table, students_requirements_table,
requirements_table, requirement_type_table
WHERE student_table.name = "Jon Smith"
AND students_requirements_table.id = student_table.id
AND requirements_table.id = students_requirements_table.requirement_id
AND requirement_type_table.id = requirements_table.requirement_type_id;

Check that the table names are accurate, as I've had to assume a couple of things (such as there being underscores in some of your table names), and note that all of the above should actually be one long line (but that makes it unreadable on this page, so I've split it across multiple lines).

I don't have a LAMP rig setup at the moment, so I can't mock this up to test it, and it's been a while since I had to write MySQL joins, but I think this is on the right track.

If you need to use LEFT JOIN then take a look at this page: Left joins to link three or more tables.

Upvotes: 2

Related Questions