Due to the help of this great forum, i got a MySql query that gives me certain data from my Joomla database.
One of the tables is called jos_rsform_submission_values.
This table looks like this:
SubmissionValueID | FormID |SubmissionID | Fieldname | Fieldvalue
…300… |…2…|…33…|…Name…| John Doe
Another table, jos_Users, looks like this:
id |…name… | username |… email … | block | gid
77 |John Doe | …John… | testATtest.com |… 1… | 18
78 |Jane Doe | …Jane… | …tryATtry.net… |… 0… | 18
The value in jos_Users.block can be a “0” or a “1”. A “0” means that the users has confirmed his identity by clicking a link in a confirmation email.
What i would like to have, and i’m not sure if this can be done, is a comparison on the jos_Rsform_Submission_Values.Name and the jos_Users.Name. The outcome of the query would be the names that have a “Block” value of “0”
This is my present query:
, ( SELECT FieldValue
WHERE FieldName = ‘Name’
AND SubmissionId = B.SubmissionId ) as Name
FROM jos_RSFORM_FORMS A
JOIN jos_RSFORM_SUBMISSIONS B
ON B.FormId = A.FormId
JOIN jos_RSFORM_SUBMISSION_VALUES D
ON D.SubmissionId = B.SubmissionId
AND D.FieldName = ‘volunteer_nr’
AND D.FieldValue = ‘11’
WHERE A.FormId = 2
I hope some can point me into the right direction on this !
Thanks in advance,