sote Posted November 18, 2005 Share Posted November 18, 2005 I want to list the students who select courses that somebody selectstable:student(id,name),course(id,name),student_course(studentid,courseid)thank you! Link to comment Share on other sites More sharing options...
Skemcin Posted November 18, 2005 Share Posted November 18, 2005 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 More sharing options...
sote Posted November 18, 2005 Author Share Posted November 18, 2005 Thank you! But I mean to list the students who are taking courses that somebody(i.e sote) is taking. Link to comment Share on other sites More sharing options...
Skemcin Posted November 18, 2005 Share Posted November 18, 2005 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 More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now