John Davidson

Message:

I have 3 tables:

• tb_user


• tb_addquestion


• tb_answer

The following queries return count of questions per user (query 1) and answers per user (query 2). I need to combine the results to a single output.

How can I achieve this with the help of a single query?

Count of questions per user (query1)

SELECT tb_addquestion.userid,

       COUNT(*) AS count

FROM tb_addquestion

LEFT JOIN tb_user

    ON tb_user.userid = tb_addquestion.userid

GROUP BY tb_addquestion.userid

HAVING count > 0 AND

       count < 15 

Answers per user (query2)

SELECT tb_answer.userid,

       COUNT(*) AS count

FROM tb_answer

LEFT JOIN tb_user

    ON tb_user.userid = tb_answer.userid

GROUP BY tb_answer.userid

HAVING count > 0 AND

       count < 15 

---

My Answer:
(*) > 0_Count of answers per user (query2)_SELECT tb_answer.userid,_ COUNT(*) AS count_FROM tb_answer_LEFT JOIN tb_user_ ON tb_user.userid = tb_answer.userid_GROUP BY tb_answer.userid_HAVING count > 0__To combine these two queries in Laravel, you can use the `DB` facade to run raw SQL queries and then merge the results using the `merge` method. Here's an example of how you can achieve this:_php$questions = DB::select("SELECT tb_addquestion.userid, COUNT(*) AS count FROM tb_addquestion LEFT JOIN tb_user ON tb_user.userid = tb_addquestion.userid GROUP BY tb_addquestion.userid HAVING count > 0");$answers = DB::select("SELECT tb_answer.userid, COUNT(*) AS count FROM tb_answer LEFT JOIN tb_user ON tb_user.userid = tb_answer.userid GROUP BY tb_answer.userid HAVING count > 0");$combined = collect($questions)->merge($answers);__In this example, we first run the two queries using the `DB::select` method to fetch the count of questions and answers per user. Then, we use the `merge` method of the `collect` helper to combine the results into a single collection. You can then iterate over the `$combined` collection to access the combined results.