Ytukyg

********* WORKING QUERY BELOW *********

SELECT  a.SubmissionId, a.FieldName, a.FieldValue

 FROM    jos_rsform_submission_values a

         INNER JOIN

         (SELECT SubmissionId, FieldName

         FROM jos_rsform_submission_values b

               WHERE FieldName IN ('Status1', 'Status2', 'Status3', 'Status4', 'Status5', 'Status6', 'Status7', 'Status8', 'Status9', 'Status10')

                AND FieldValue = 'Pending'

                 AND FormId = 28

          ) AS test



         ON a.SubmissionId = test.SubmissionId

WHERE  a.FieldName = 'bayanno1' AND a.FormId = 28

*************END*************

I have written the following SQL query:

SELECT SubmissionId,

   (SELECT FieldValue

      FROM jos_rsform_submission_values

     WHERE FieldName IN (SELECT FieldName

                           FROM jos_rsform_submission_values

                          WHERE FieldName = 'bayanno1')) AS FieldValue

  FROM jos_rsform_submission_values

 WHERE FieldName IN (SELECT FieldName

                       FROM jos_rsform_submission_values

                      WHERE FieldName = 'Status1')

   AND FieldValue = 'Pending'

   AND FormId = 28

I was just wondering if someone could possibly help me in finding the issue of why it's giving me the following error:

#1242 - Subquery returns more than 1 row

I have two fields in the same column. One is "bayanno1" and the other is "Status1".

Each of these have a value when a form is submitted on the website. All submissions are linked with a "SubmissionId". This means that if I have 500 submissions of the form, it won't replace the "Status1" value from "SubmissionID" 150 with the "bayanno1" value from "SubmissionId" 120.

For example:

(FieldName) "bayanno1"  = "12345" (FieldValue) ; 300 (SubmissionId) ;



(FieldName) "Status1" = "Pending" (FieldValue) ; 300 (SubmissionId) ;

I need to replace the VALUE of "Status1" with the VALUE of "bayanno1"; but only for query purposes. I don't want to replace the actual value, only as a visual query.

SO the table with the correct SQL should look like this (I will write a PHP query on my webpage to extract the info from the database and insert into a table, however this table is just a very basic example):

<table>

<tr>

<th>bayanno1</th>

<th>Status1</th>

</tr>

<tr>

<td>12345</td>

<td>12345</td>

</tr>

</table>

Edited -- after you have changed the logical requirements of your question...

In simplest terms, you want to extract the FieldValue column value from the rows that contain 28 as the FormId and banyanno1 as the FieldName. Furthermore, you want to filter those results to only show SubmissionIds which have Pending as the value in any of your columns that start with Status (assuming you only have those 10 possibilities).

SELECT a.SubmissionId, a.FieldValue

FROM jos_rsform_submission_values a

INNER JOIN

    (SELECT SubmissionId

     FROM jos_rsform_submission_values

     WHERE FieldName LIKE 'Status%'

       AND FieldValue = 'Pending'

       AND FormId = 28

    ) b

ON a.SubmissionId = b.SubmissionId

WHERE a.FormId = 28

  AND a.FieldName = 'bayanno1'

If you want to see all of the columns generated by the join, you can use a.*, b.* in your SELECT clause.

As I have told you repeated times before, when you are seeking MySQL support here or on JoomlaStackExchange, please accompany your question with a sufficient amount of table structure and data so that volunteers who do not have a local copy of the rs_forms_submission_values table can have an easier time of offering assistance. More importantly, volunteers will have no idea that you have 10 different Status rows in your data, so they are doomed to not only fail, but waste their time trying to help you.

If you don't believe me, listen to Strawberry.

Finally, I think you would have received faster support at JoomlaStackExchange because many of the volunteers there are likely to have their own local copy of the table and could write and test solutions directly on their own systems. This is a Joomla related question, so it would certainly be welcome at JSE.