mysql distinct query using joins

Question

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!

Bobulous · Accepted Answer · 2012-09-01 20:16:39Z

2

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.

answered Sep 1, 2012 at 20:16

Bobulous

13.2k5 gold badges39 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Barmar Over a year ago

MySQL doesn't require everything to be on one line. And if you need a place to mock it up, use www.sqlfiddle.com.

Sean Over a year ago

Hey, i realize I am behind on getting back to you on this. I just didn't want your help to go un-noticed. This query did in fact work for me. My problem now is handling the returned info via php. NOt quite as simple :)

Collectives™ on Stack Overflow

mysql distinct query using joins

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related