i have a table users(user_id, username, password etc), a table courses(course_id, coursename, etc) and a table student_favouriteCourses(user_id, course_id) //both as primary key
and i would like if i know the id of the user, to search for all the courses that he has as favourites. can u help me with the join query please?
Maybe something like this:
SELECT
*
FROM
student_FavouriteCourse fav
JOIN student stu ON ( fav.user_id = stu.user_id)
JOIN courses crs ON ( fav.course_id = crs.course_id)
WHERE
fav.user_id = <yourvalue>
As an explanation:
1) this will return the student_FavouriteCourse (using "fav" as an alias) records you want
SELECT
*
FROM
student_FavouriteCourse fav
WHERE
fav.user_id = <yourvalue>
2) you then JOIN the student details (using "stu" as an alias)
JOIN student stu ON ( fav.user_id = stu.user_id)
3) you then JOIN the course details (using "crs" as an alias)
JOIN courses crs ON ( fav.course_id = crs.course_id)
Can I suggest that if you are new to JOINs and SQL, you run this query at each of steps above and then you will see how the join is working and how the resulting dataset is constructed.
EDIT: Read your post again and I see you don't actually need the student join, just the courses. Therefore:
SELECT
*
FROM
student_FavouriteCourse fav
JOIN courses crs ON ( fav.course_id = crs.course_id)
WHERE
fav.user_id = <yourvalue>
I think your trying a very basic MySQL query, can you please next time try to put your code in so we can help in your mistakes and you can learn from them?
Anyway the code youre trying to do should be something like this:
select u.id, u.username, c.coursename
from users u
left join student_favouriteCourses f on u.id = f.user_id
left join courses c on f.course_id = c.id
where u.username = '<YourUsername>'
This will return your user join with your favourite courses and then the course.
In case you want to make sure you show at least 1 course (confirm that course exist) inner join will help you:
select u.id, u.username, c.coursename
from users u
inner join student_favouriteCourses f on u.id = f.user_id
inner join courses c on f.course_id = c.id
where u.username = '<YourUsername>'
There are many ways to do those joins depends of your scenario and how you like to code that.
I leave you here a link that explains this much better.
