many to many relationship mysql select
- by zeina
Let's consider 2 tables "schools" and "students". Now a student may belong to different schools in his life, and a school have many students. So this is a many to many example. A third table "links" specify the relation between student and school.
Now to query this I do the following:
Select sc.sid , -- stands for school id
st.uid, -- stands for student id
sc.sname, -- stands for school name
st.uname, -- stands for student name
-- select more data about the student joining other tables for that
from students s
left join links l on l.uid=st.uid -- l.uid stands for the student id on the links table
left join schools sc on sc.sid=l.sid -- l.sid is the id of the school in the links table
where st.uid=3 -- 3 is an example
this query will return duplicate data for the user id if he has more than one school, so to fix this I added group by st.uid, yet I also need the list of school name related to the same user. Is there a way to do it with fixing the query I wrote instead of having 2 queries? So as example I want to have Luci of schools ( X, Y, Z, R, ...) etc
