SIGN UP

SQL Unmatched Query Help

By: Richard Posner | Asked: 08/27/2024
Forums › Category: General questions › SQL Unmatched Query Help
0 Vote Up Vote Down
Richard Posner asked 4 months ago
I have 2 forms I am trying to use an Unmatched Query to find out which users have completed the first form, but have not submitted the 2nd form. This query doesn't fail, (The syntax seems to be good)  but does not yield any values even though I know there are some users that have not submitted the second form.     The Field ID on the first form is 17 The Field ID on second form that represents the User ID is 4125 SELECT Y.USER_ID
FROM wp_frm_items Y, wp_frm_item_metas Z
LEFT OUTER JOIN wp_frm_item_metas AS Z1 On Z.item_id = Z1.item_id
Where Y.id = Z.item_id
AND Y.id = Z1.item_id
And Z.field_id In (17)
And Z.meta_value = "2024-08-23"
And Z1.field_id In (4125)
AND Z1.META_VALUE Is Null   Any help will be appreciated.
5 Answers
0 Vote Up Vote Down
Richard Posner answered 4 months ago 
Start your code here
SELECT Y.USER_ID 
FROM wp_frm_items Y, wp_frm_item_metas Z
LEFT OUTER JOIN wp_frm_item_metas AS Z1 On Z.item_id = Z1.item_id 
Where Y.id = Z.item_id
AND Y.id = Z1.item_id
And Z.field_id In (17) 
And Z.meta_value = "2024-08-23" 
And Z1.field_id In (4125)
AND Z1.META_VALUE Is Null
0 Vote Up Vote Down
Victor Font answered 4 months ago
You may be going about this incorrectly. Z1.META_VALUE Is Null will never be true. Formidable does not save fields without content. You are creating your LEFT JOIN on Z.item_id = Z1.item_id but Z is form A and Z1 is Form B. It's impossible for two forms to match on the same entry id when everything links back to Form A's entry id ( Y.id = Z.item_id ).
Victor Font replied 4 months ago

As a general rule, write your SQL in PHPMyAdmin and get it working there first. Once you get it working, copy it and refactor it for Formidable. You also don't mention how you're executing this code.

0 Vote Up Vote Down
Richard Posner answered 4 months ago
Victor, I am executing the sql in my hosts SQL window which is  PHPMyAdmin The first SQL below works and returns all of the users that have submitted form 1 I understand what you are saying,  Can you please help me understand how I can make the left outer join return the user_id for those that have not submitted the second form?     
Start your code here
First SQL
SELECT Y.USER_ID 
FROM wp_frm_items Y, wp_frm_item_metas Z
Where Y.id = Z.item_id
And Z.field_id In (17) 
And Z.meta_value = "2024-08-23" 


My try

SELECT Y.USER_ID 
FROM wp_frm_items Y, wp_frm_item_metas Z
LEFT OUTER JOIN wp_frm_item_metas AS Z1 On Z.item_id = Z1.item_id 
Where Y.id = Z.item_id
AND Y.id = Z1.item_id
And Z.field_id In (17) 
And Z.meta_value = "2024-08-23" 
And Z1.field_id In (4125)
AND Z1.META_VALUE Is Null
   
Victor Font replied 4 months ago

You don’t need anything from frm_item_metas if all your doing is checking if an entry exists for form b. Just check frm_items for an entry in form b for the targeted user id. If your SQL returns null, there's no entry. If SQL returns a value, there's an entry. This is all you need in PHPMyAdmin:

SELECT * FROM `wp_frm_items` WHERE form_id = "2" and user_id = "4216";

Formidable has ways of retrieving what you want through various hooks.

Where in the process do you want to know the existence of an entry in form b?

0 Vote Up Vote Down
Richard Posner answered 4 months ago
Victor, I got something to work and I appreciate your helping me get away from the Unmatched trials.   I do have another issue with this SQL Statement and perhaps you know how it can be resolved.   The output of this SQL Statement yields every field on the Z1 form (Basically Form 2) and does it vertically.  I would like to limit the output to just some fields in the table and have the output horizontally e.g. User ID, First Name, Last Name and not Uer ID, First Name Last Name   when I tried specifying a field id for Y1 the query returned empty   I know how to do this in a more traditional database, but not here.  Can you help?           
Start your code here
SELECT Y.USER_ID, Z1.meta_value
FROM wp_frm_items Y, wp_frm_item_metas Z, wp_frm_item_metas Z1
Where Y.id = Z.item_id
And Y.id = Z1.item_id
And Y.form_id In (2) 
And Z.field_id In (17) 
And Z.meta_value = "2024-08-23"
And Z1.field_id In (4077) 
And Y.USER_ID Not In(
SELECT Y.USER_ID 
FROM wp_frm_items Y, wp_frm_item_metas Z
Where Y.id = Z.item_id
And Y.form_id In (43) 
And Z.field_id In (4072))
0 Vote Up Vote Down
Richard Posner answered 4 months ago
Thanks to all that have helped.   I have used the following to return all of the User's First Name, Last Name, Email and User ID that have been registered, but have not submitted the Detail form.  It works great.     This clause captures the user's First Name, Last Name, Email Address and User ID - Change Field ID's as appropriate SELECT Z.meta_value, Z1.meta_value, Z2.meta_value, Y.user_id
FROM wp_frm_items Y, wp_frm_item_metas Z, wp_frm_item_metas Z1 , wp_frm_item_metas Z2
Where Y.id = Z.item_id
And Y.id = Z1.item_id
And Y.id = Z2.item_id
And Z.field_id In (6) 
And Z1.field_id In (7) 
And Z2.field_id In (9)   This clause filters the output by the registration date - Change the Field ISD's as appropriate And Y.user_id In (SELECT Y.USER_ID 
FROM wp_frm_items Y, wp_frm_item_metas Z
Where Y.id = Z.item_id
And Y.form_id In (2) 
And Z.field_id In (17) 
And Z.meta_value = "2024-08-23") This clause filters the output by excluding the users that have completed the Registration form and the Detail form - Change the Field ISD's as appropriate
And Y.USER_ID Not In(
SELECT Y.USER_ID 
FROM wp_frm_items Y, wp_frm_item_metas Z2
Where Y.id = Z2.item_id
And Y.form_id In (43) 
And Z2.field_id In (4072))   I then have exported the output of this to and Excel/CSV Created a form with these 4 fields with me the admin as the user that only allows a single entry Imported the csv file to the form Then created a View from the form data Published the View to a Page   This all works, is a bit cumbersome, and doesn't update in real-time because the Excel/CSV and import must be repeated daily, but at least it gives me the data that I need.

Making the Best WordPress Plugin even better - Together

Take on bigger projects with confidence knowing you have access to an entire community of Formidable Experts and Professionals who have your back when the going gets tough. You got this!
Join the community
crossarrow-right