Compare fieldvalue to columnvalues

Databases
1

Hi,

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 A.FormId
, B.SubmissionId
, B.DateSubmitted
, ( SELECT FieldValue
FROM jos_RSFORM_SUBMISSION_VALUES
WHERE FieldName = ‘Name’
AND SubmissionId = B.SubmissionId ) as Name

FROM jos_RSFORM_FORMS A

INNER
JOIN jos_RSFORM_SUBMISSIONS B
ON B.FormId = A.FormId

INNER
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,
Mesjoggah

2

you neglected to say what was wrong with your present query

you explained the jos_rsform_submission_values table but not the A and B tables in the query

3

One is never too old to learn.
After some time of reading, trial and error, i managed to complete my query.
If anyone has some comments or advise, i’ll be pleased to hear them !

SELECT A.FormId
, B.SubmissionId
, B.DateSubmitted
, B.UserIp
, ( SELECT FieldValue
FROM jos_RSFORM_SUBMISSION_VALUES
WHERE FieldName = ‘Name’
AND SubmissionId = B.SubmissionId ) as Naam
, ( SELECT FieldValue
FROM jos_RSFORM_SUBMISSION_VALUES
WHERE FieldName = ‘adres’
AND SubmissionId = B.SubmissionId ) as Adress
, ( SELECT FieldValue
FROM jos_RSFORM_SUBMISSION_VALUES
WHERE FieldName = ‘telefoon’
AND SubmissionId = B.SubmissionId ) as Telephone
, ( SELECT FieldValue
FROM jos_RSFORM_SUBMISSION_VALUES
WHERE FieldName = ‘email’
AND SubmissionId = B.SubmissionId ) as Email

FROM jos_RSFORM_FORMS A

INNER
JOIN jos_RSFORM_SUBMISSIONS B
ON B.FormId = A.FormId

INNER
JOIN jos_RSFORM_SUBMISSION_VALUES C
ON C.SubmissionId = B.SubmissionId

INNER
JOIN jos_USERS D
on D.name = C.Fieldvalue
AND D.Block = ‘0’

INNER
JOIN jos_RSFORM_SUBMISSION_VALUES E
ON E.SubmissionId = B.SubmissionId
AND E.FieldName = ‘Volunteer_nr’
AND E.FieldValue = ‘11’

INNER
JOIN jos_RSFORM_SUBMISSION_VALUES F
ON F.SubmissionId = B.SubmissionId
AND F.FieldName = ‘capaciteit’
AND F.FieldValue LIKE ‘%10-20%’

WHERE A.FormId = 2