I have a problem regarding mysql.
I have few tables like course
, papers
, students
, studentspapers
Now what I want is that my query should return the result like:
It should select student name, student category from students table, students optional paper from studentspaper but I have not mentioned anything in my studentpapers table but in papers table.
Papers table have id as primary key which I have used in studentspaper as foreign key.
Now the simple query I am using is:
select
st.name,
s.paperid
from
students st,
sps s join papers
on paperid
where
paperid = (select p.p_id from papers p where type='optional')
and st.course=1
I dont know what shall I do.
Its saying subquery return more than 1 row #1242
Your query is:
select st.name, s.paperid
from students st,
sps s join
papers
on paperid
where paperid= (select p.p_id from papers p where type='optional') and st.course=1
It is missing a join between students and sps. I think you mean:
select st.name, s.paperid
from students st join
sps s
on st.studentid = sps.studentid join
papers p
on p.paperid = sps.paperid
where p.type='optional' and st.course=1
However, without seeing the structure of the tables, it is hard to say if this is the right query.