Search code examples
mysqloracleselectcursor

MySQL select, embedded cursor

For example I have database, where I have two tables QUESTIONS and ANSWERS. One question can have multiple answers, so QUESTIONS table has one to many relation with ANSWERS table.

In Oracle to select one question, with all it's answers I can write following select:

select q.*,
       cursor (select a.*
               from ANSWRES a
               where a.question_id = q.question_id) as answers
from QUESTIONS q
where q.question_id = 123;

Is it possible to write similar select in MySQL database ?

If not, what are alternative ways two get question, with all it's answers using one select ?

Solution

  • You should be able to do this with a join:

    select q.*, a.*
from QUESTIONS q left outer join
     ANSWERS a
     on a.question_id = q.question_id
where q.question_id = 123;