SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Compare fieldvalue to columnvalues

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •