I was wondering if anyone could help me JOIN multiple queries into one query. Currently, these 2 blocks of code retrieve information from the DB. I am using the "Sourcerer" plugin to add custom PHP code in my custom module.

However, the full query is much too long, and I need to reduce the size considerably. After a certain amount of queries, the page just doesn't load at all.

I will post two queries I need to have joined as an example.

$db = JFactory::getDbo();

$db->setQuery("SELECT `SubmissionId`, `FieldValue`, `FormId` FROM `jos_rsform_submission_values` WHERE `FieldName` = \"Status1\" AND `FieldValue` = \"Pending\" AND `FormId` = 28");
$results = $db->loadColumn();

$db->setQuery("SELECT `FieldValue` FROM `jos_rsform_submission_values` WHERE `FieldName` = \"Container1\" AND `SubmissionId` IN ('".implode("', '", $results)."') ORDER BY `jos_rsform_submission_values`.`SubmissionId`"); 

$result = $db->loadObjectList();

foreach($result as $value) {
    foreach($value as $key => $data) { 
        echo $data."<br />"; 
  • I'll try and write more details for the question. I literally just need to put those 2 queries into one query. That is all. I'll add some more details though.
    – MailBlade
    Commented Jul 25, 2018 at 12:59

2 Answers 2


Here is what I believe the mySQL query to be with (1,2,3) substituted in for your imploding of results because I wanted to check the mySql syntax of it. I don't have time to convert into PHP right now, but wanted to try to help. If it doesn't work, let me know in comments and I will try to adjust:

SELECT a.FieldValue as Container, b.FieldValue as ContainerType FROM ( SELECT FieldValue FROM jos_rsform_submission_values WHERE FieldName = "Container1" AND SubmissionId IN (1,2,3)) as a LEFT JOIN ( SELECT FieldValue FROM jos_rsform_submission_values WHERE FieldName = "containertype1" AND SubmissionId IN (1,2,3)) as b on a.SubmissionId = b.SubmissionId ORDER BY a.SubmissionId;

I had to remove the tick marks to get StackExchange to format correctly, but I would suggest converting your code to use $db->quote and $dbquotename instead anyway: https://docs.joomla.org/Selecting_data_using_JDatabase

  • Thank you so much for your reply mate!! I will give it a go and let you know :)
    – MailBlade
    Commented May 31, 2018 at 10:52
  • I know this is 2 months late, but I was wondering if you could post the PHP code for this?
    – MailBlade
    Commented Jul 17, 2018 at 12:57
  • I'm pretty slammed this week but will take a look when I get some time. Commented Jul 18, 2018 at 18:19

Yes, your queries can certainly use refactoring. Using two separate calls on the same table is asking MySQL to do needless extra work.

To convert your queries into plain English, you want to:

  • create a result set of containertype1 values sorted by SubmissionId
  • for each SubmissionId from FormId 28
  • including only submissions that contain a Status1 value of Pending.

The cleanest, most direct way to perform this task is with a "pivot" technique.

Raw Query (untested):

    MAX(CASE WHEN FieldName = 'containertype1' THEN FieldValue ELSE NULL END)
FROM `#__rsform_submission_values`
WHERE FormId = 28
GROUP BY SubmissionId
HAVING MAX(CASE WHEN FieldName = 'Status1' THEN FieldValue ELSE NULL END) = 'Pending'
ORDER BY SubmissionId

GROUP BY creates "aggregate data" for each unique SubmissionId. Extracting specific details from within this collection of consolidated rows requires specialized calls (e.g.MAX(CASE...)).

The HAVING clause requires SubmissionIds to contain a row with a FieldName of Status1 and a FieldValue value of Pending. This relies on the fact that your submission_values table does not allow a SubmissionId to have 2 or more Status1 rows.

For every qualifying SubmissionId, the result set will provide the value that is stored with containertype1. If the qualifying SubmissionId does not have a containertype1 row, a NULL value will be provided as the default (you could change NULL to No Container Type if you wanted).

PHP/Joomla Syntax (untested):

$query = $db->getQuery(true)
    ->select("MAX(CASE WHEN FieldName = 'containertype1' THEN FieldValue ELSE NULL END)")
    ->where("FormId = 28")
    ->having("MAX(CASE WHEN FieldName = " . $db->q("status") . " THEN FieldValue ELSE NULL END) = " . $db->q("pending"));

// echo $query->dump();  // uncomment if you want to confirm the rendered query
try {
    echo "<pre>";
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');  // never show getMessage() to public

If you would like to see this technique in other contexts, here are some other pivot solutions that I have posted:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.