Mysql: Query in Select

Question

I have 3 tables say result ,person and person_detail:

Result has 4 columns id, teacher (fk from person) , student(fk from person), percent

Person has many columns id, address....etc.

person_detail has 3 column id, name, person(fk for person)

I want my result in something like :

teacher.name, student.name, percent

For this I tried out this query:

select 
(select pd.name from Person_detail pd where pd.person=r.teacher) as Madam,
(select pd.name from Person_detail pd where pd.person=r.student) as Student,
and r.percent from Result r;

But this is throwing me exception as sub query returns more then 1 row and on some of my friends query browser result is as message "ok".

Please can someone provide me solution for this?

Thanks in advance,

Answer 1

Your subquery must return unique row , In your query either this select pd.name from Person_detail pd where pd.person=r.teacher or this select pd.name from Person_detail pd where pd.person=r.student returning multiple rows.

So if that result multiple rows you will get above error. If you want to get unique result then improve your where condition or use Top(1) to get unique row(Not recommended).

You can write above query without subquery like This

Answer 2

you need to use join, between result and two instances of person_details (one for student and one for teacher):

select pdt.name as madam, pds.name as student, r.percent from
result as r inner join person_detail as pdt on r.teacher = pdt.person 
inner join person_detail as pds on r.student = pds.person