Search code examples
sqljoomlaphpmyadmin

List duplicate results on individual rows?

I have a query which extracts the following information from the database:

Database table image

Now, as you can see I have added a GROUP_CONCAT to the query, and in the GROUP_CONCAT column by SubmissionId 455, there are 2 values.

THE DESIRED RESULT

What I am trying to achieve is to list each value individually, WHERE it has the SAME SubmissionID or SAME FieldValue or SAME SubmissionValueId

The table should look like this:

SubmissionValueId --- FieldName ---   FieldValue   ---   SubmissionId --- FormId --- GROUP_CONCAT

52133              portofloading1   King Abdullah Port       399            28     King Abdullah Port
64318              portofloading1   Jeddah Seaport           419            28     Jeddah Seaport
74723              portofloading1   King Abdullah Port       439            28     King Abdullah Port
79269              portofloading1   Jeddah Seaport           444            28     Jeddah Seaport
87144    ***       portofloading1   Jeddah Seaport           455            28     Jeddah Seaport
87144    ***       portofloading1   Jeddah Seaport           455            28     Jeddah Seaport
87663              portofloading1   King Abdullah Port       456            28     King Abdullah Port
105786             portofloading1   King Abdullah Port       501            28     King Abdullah Port
109419             portofloading1   King Abdullah Port       508            28     King Abdullah Port
120521             portofloading1   King Abdullah Port       528            28     King Abdullah Port
129869             portofloading1   Jeddah Seaport           548            28     Jeddah Seaport

In the table where I marked the rows with *** are basically what I need to do. I need to list each row individually, even if it is a duplicate (I don't know if this is possible)

Here is the code for retrieving the values (image data):

SELECT a.SubmissionValueId, a.FieldName, a.FieldValue, a.SubmissionId, a.FormId, GROUP_CONCAT(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')
             AND FieldValue = 'Pending'
              AND FormId = 28
         ) AS test
         ON a.SubmissionId = test.SubmissionId
WHERE  a.FieldName = 'portofloading1' AND a.FormId = 28
GROUP BY SubmissionId

The reason I need to list each one individually is that I need to retrieve the portofloading value for each field where a `Status` = 'Pending'. So if `Status1` = 'Pending', it will list the portofloading1 where the SubmissionId of Status1 && portofloading are EQUAL.

The issue is sometimes you can have `Status1` = 'Pending' ; `Status2` = 'Pending' ; `Status3` = 'Pending' ; all for the SAME SubmissionId.

All Status% fields up to Status5 are "linked" to portofloading1. So this means that if Status1 - Status3 are all 'Pending' for a SubmissionId ; the query should list each row individually, even though all three fields have the same portofloading1, same SubmissionValueId and same SubmissionId ; IF you use the above query to retrieve the data and use the INNER JOIN function on SubmissionId.

I honestly don't know how to put better into words what I require. I hope it made some sense.

I am trying to be more thorough in explaining the issue and provided a snapshot of the database running this query to hopefully make it more clear.

Solution

  • You can try below - remove group_concat() and group by clause

    SELECT a.SubmissionValueId, a.FieldName, a.FieldValue, a.SubmissionId, a.FormId, 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')
             AND FieldValue = 'Pending'
              AND FormId = 28
         ) AS test
         ON a.SubmissionId = test.SubmissionId
WHERE  a.FieldName = 'portofloading1' AND a.FormId = 28