********* 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>
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.