Jump to content

Please have a look


sote

Recommended Posts

select s.name, c.namefrom student sjoin student_course sc on sc.studentid = s.idjoin course c on c.id = sc.courseid(that should be pretty close)I'd recommend a better naming convention for your tables . . . ab_student- stu_id- stu_nameab_course- cor_id- cor_nameab_student_x_course- sxc_id- sxc_stu_id- sxc_cor_idThis would make your query easier to read/manageselect stu_name, cor_namefrom ab_studentjoin ab_student_x_course on sxc_stu_id = stu_idjoin ab_course on cor_id = sxc_cor_idIt takes a little confusion out of the aliases. Since most people don't document their aliases, their use can be inconsistan across a site and therefore make things sometimes hard to follow.This type of prefix naming convention allows you to avoid aliases since you will always have unique names. I'd also suggest using a prefix in you tables names as well - essentially grouping tables by their prefix.Let me know how that works out.

Link to comment
Share on other sites

Thank you! But I mean to list the students who are taking courses that somebody(i.e sote) is taking.

then add a WHERE clause:SELECT stu_name, cor_nameFROM ab_student JOIN ab_student_x_course ON sxc_stu_id = stu_id JOIN ab_course ON cor_id = sxc_cor_idWHERE stu_name LIKE 'sote'That will look for the name exactly matching "sote". You can add a wild card to either side if you like:-- begins with "sote"WHERE stu_name LIKE 'sote%'-- ends with "sote"WHERE stu_name LIKE '%sote'-- contains "sote"WHERE stu_name LIKE '%sote%'P.S. If you would like a line by line explanation of each part of the SQL statement, let me know.
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...