CODEWITHSUNDEEP
mysqlsqlselect
heron
heron

Reputation: 3661

How to merge 2 sql statements and get results at once

I can't figure out how to merge these 2 queries and get result for them at once.

        $q1 = "SELECT q.id, q.content, a.id, a.content, a.addDT, acr.score 
            FROM questions_and_exercises q, `lesson-question` lq 
            LEFT JOIN answers a ON (a.uid=? AND a.qid=lq.qid)
            LEFT JOIN `answer_chk_results` acr ON (acr.aid=a.id)
            WHERE q.id=lq.qid AND lq.lid=?";
        $q2 ="SELECT q.id, q.content, a.id, a.content, a.addDT, acr.score
             FROM questions_and_exercises q,
             `questions from-to` qft
            LEFT JOIN answers a ON (a.uid=? AND a.qid=qft.qid)
            LEFT JOIN `answer_chk_results` acr ON (acr.aid=a.id)
            WHERE q.id=qft.qid AND qft.to_uid=?";

Suggetions? Thanks in advance

Upvotes: 0

Views: 103

Answers (2)

Robin Castlin
Robin Castlin

Reputation: 10996

Use the UNION for this matter.

However in this case, I don't think an UNION is even needed:

$q1 = "SELECT q.id, q.content, a.id, a.content, a.addDT, acr.score 
        FROM questions_and_exercises q, `lesson-question` lq 
        LEFT JOIN answers a
        ON a.qid=lq.qid
        LEFT JOIN `answer_chk_results` acr 
        ON acr.aid=a.id
        WHERE q.id=lq.qid AND a.uid = ? (lq.lid = ? OR qtf.to_uid = a.uid)";

It will smash together these 2 results into more rows.

$q1 = "SELECT q.id, q.content, a.id, a.content, a.addDT, acr.score 
        FROM questions_and_exercises q, `lesson-question` lq 
        LEFT JOIN answers a ON (a.uid=? AND a.qid=lq.qid)
        LEFT JOIN `answer_chk_results` acr ON (acr.aid=a.id)
        WHERE q.id=lq.qid AND lq.lid=?
        UNION # ALL
        SELECT q.id, q.content, a.id, a.content, a.addDT, acr.score
         FROM questions_and_exercises q,
         `questions from-to` qft
        LEFT JOIN answers a ON (a.uid=? AND a.qid=qft.qid)
        LEFT JOIN `answer_chk_results` acr ON (acr.aid=a.id)
        WHERE q.id=qft.qid AND qft.to_uid=?";

Either use UNION or UNION ALL depending on if you want to merge identical rows or not. An UNION requires the two SELECTs to have the same amount of columns, and will use the first SELECTs aliases.

You could easen up last query like this:

        SELECT q.id, q.content, a.id, a.content, a.addDT, acr.score
        FROM questions_and_exercises q,
        `questions from-to` qft
        LEFT JOIN answers a 
        ON a.qid=qft.qid
        LEFT JOIN `answer_chk_results` acr 
        ON acr.aid=a.id
        WHERE q.id=qft.qid AND qft.to_uid=? AND qtf.to_uid = a.uid

Basicly since it's the same value, it's enough to compare to_uid with ?, then compare to_uid with uid. It's the same result as comparing them through ON.

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

Use Union all

$q1 = "SELECT q.id, q.content, a.id, a.content, a.addDT, acr.score  
            FROM questions_and_exercises q, `lesson-question` lq  
            LEFT JOIN answers a ON (a.uid=? AND a.qid=lq.qid) 
            LEFT JOIN `answer_chk_results` acr ON (acr.aid=a.id) 
            WHERE q.id=lq.qid AND lq.lid=? union all
            SELECT q.id, q.content, a.id, a.content, a.addDT, acr.score 
             FROM questions_and_exercises q, 
             `questions from-to` qft 
            LEFT JOIN answers a ON (a.uid=? AND a.qid=qft.qid) 
            LEFT JOIN `answer_chk_results` acr ON (acr.aid=a.id) 
            WHERE q.id=qft.qid AND qft.to_uid=?";

Upvotes: 0

Related Questions