|
Re:SQL Query getting form ID, but not submitted data 12 Years, 1 Month ago
|
Karma: 748
|
Use the next query:
| Code: |
SELECT
SV_Date.FieldValue AS Date,
SV_FirstName.FieldValue AS FirstName,
SV_LastName.FieldValue AS LastName,
SV_EventNumber.FieldValue AS EventNumber
FROM
#__rsform_submission_values SV_Date
LEFT JOIN #__rsform_submission_values SV_FirstName
ON SV_Date.SubmissionId = SV_FirstName.SubmissionId
LEFT JOIN #__rsform_submission_values SV_LastName
ON SV_Date.SubmissionId = SV_LastName.SubmissionId
LEFT JOIN #__rsform_submission_values SV_EventNumber
ON SV_Date.SubmissionId = SV_EventNumber.SubmissionId
WHERE
SV_Date.FieldName = 'Date'
AND
SV_FirstName.FieldName = 'First Name'
AND
SV_LastName.FieldName = 'Last Name'
AND
SV_EventNumber.FieldName = 'Event Number'
GROUP BY
SV_Date.SubmissionId
|
Regards,
ARI Soft |
|
|
|
|
|
|
Re:SQL Query getting form ID, but not submitted data 12 Years, 1 Month ago
|
Karma: 0
|
Thank you, that helped!
Is there a limit as to how many columns there can be? I have 32 columns total and the finished code below takes a long time on the front-end and then shows "No data available." Am I missing something? I am also selecting the "ARI Data Table" as table type.
I also thought about empty fields... would empty fields also generate broken code on the front end? Can we keep empty fields or do we have to have it say "Not Selected" instead?
Thanks again for all your help! It is greatly appreciated.
| Code: |
SELECT
SV_Date.FieldValue AS Date,
SV_FirstName.FieldValue AS FirstName,
SV_LastName.FieldValue AS LastName,
SV_EventNumber.FieldValue AS EventNumber,
SV_CaseNumber.FieldValue AS CaseNumber,
SV_DutyStatus.FieldValue AS DutyStatus,
SV_Followup.FieldValue AS Followup,
SV_TimeReceived.FieldValue AS TimeReceived,
SV_TimeArrived.FieldValue AS TimeArrived,
SV_TimeCleared.FieldValue AS TimeCleared,
SV_TotalTimeOnCall.FieldValue AS TotalTimeOnCall,
SV_PrimaryOfficersDetail.FieldValue AS PrimaryOfficersDetail,
SV_StartMileage.FieldValue AS StartMileage,
SV_EndMileage.FieldValue AS EndMileage,
SV_AreaServed.FieldValue AS AreaServed,
SV_TotalServed.FieldValue AS TotalServed,
SV_DispatchedAddress.FieldValue AS DispatchedAddress,
SV_DispatchedZip.FieldValue AS DispatchedZip,
SV_PrimaryContact.FieldValue AS PrimaryContact,
SV_PrimaryContactPhone.FieldValue AS PrimaryContactPhone,
SV_PrimaryAddress.FieldValue AS PrimaryAddress,
SV_PrimaryZip.FieldValue AS PrimaryZip,
SV_Gender.FieldValue AS Gender,
SV_Ethnicity.FieldValue AS Ethnicity,
SV_CommunityRelations.FieldValue AS CommunityRelations,
SV_GroupCounseling.FieldValue AS GroupCounseling,
SV_FamilyCounseling.FieldValue AS FamilyCounseling,
SV_AddictionsCounseling.FieldValue AS AddictionsCounseling,
SV_Accident.FieldValue AS Accident,
SV_Death.FieldValue AS Death,
SV_EmotionalProblem.FieldValue AS EmotionalProblem,
SV_FamilyStress.FieldValue AS FamilyStress,
SV_ViolentCrime.FieldValue AS ViolentCrime,
SV_OfficerSupport.FieldValue AS OfficerSupport,
SV_Synopsis.FieldValue AS Synopsis,
SV_GeneratedCaseNumber.FieldValue AS GeneratedCaseNumber
FROM
#__rsform_submission_values SV_Date
LEFT JOIN #__rsform_submission_values SV_FirstName
ON SV_Date.SubmissionId = SV_FirstName.SubmissionId
LEFT JOIN #__rsform_submission_values SV_LastName
ON SV_Date.SubmissionId = SV_LastName.SubmissionId
LEFT JOIN #__rsform_submission_values SV_EventNumber
ON SV_Date.SubmissionId = SV_EventNumber.SubmissionId
LEFT JOIN #__rsform_submission_values SV_CaseNumber
ON SV_Date.SubmissionId = SV_CaseNumber.SubmissionId
LEFT JOIN #__rsform_submission_values SV_DutyStatus
ON SV_Date.SubmissionId = SV_DutyStatus.SubmissionId
LEFT JOIN #__rsform_submission_values SV_Followup
ON SV_Date.SubmissionId = SV_Followup.SubmissionId
LEFT JOIN #__rsform_submission_values SV_TimeReceived
ON SV_Date.SubmissionId = SV_TimeReceived.SubmissionId
LEFT JOIN #__rsform_submission_values SV_TimeArrived
ON SV_Date.SubmissionId = SV_TimeArrived.SubmissionId
LEFT JOIN #__rsform_submission_values SV_TimeCleared
ON SV_Date.SubmissionId = SV_TimeCleared.SubmissionId
LEFT JOIN #__rsform_submission_values SV_TotalTimeOnCall
ON SV_Date.SubmissionId = SV_TotalTimeOnCall.SubmissionId
LEFT JOIN #__rsform_submission_values SV_PrimaryOfficersDetail
ON SV_Date.SubmissionId = SV_PrimaryOfficersDetail.SubmissionId
LEFT JOIN #__rsform_submission_values SV_StartMileage
ON SV_Date.SubmissionId = SV_StartMileage.SubmissionId
LEFT JOIN #__rsform_submission_values SV_EndMileage
ON SV_Date.SubmissionId = SV_EndMileage.SubmissionId
LEFT JOIN #__rsform_submission_values SV_AreaServed
ON SV_Date.SubmissionId = SV_AreaServed.SubmissionId
LEFT JOIN #__rsform_submission_values SV_TotalServed
ON SV_Date.SubmissionId = SV_TotalServed.SubmissionId
LEFT JOIN #__rsform_submission_values SV_DispatchedAddress
ON SV_Date.SubmissionId = SV_DispatchedAddress.SubmissionId
LEFT JOIN #__rsform_submission_values SV_DispatchedZip
ON SV_Date.SubmissionId = SV_DispatchedZip.SubmissionId
LEFT JOIN #__rsform_submission_values SV_PrimaryContact
ON SV_Date.SubmissionId = SV_PrimaryContact.SubmissionId
LEFT JOIN #__rsform_submission_values SV_PrimaryContactPhone
ON SV_Date.SubmissionId = SV_PrimaryContactPhone.SubmissionId
LEFT JOIN #__rsform_submission_values SV_PrimaryAddress
ON SV_Date.SubmissionId = SV_PrimaryAddress.SubmissionId
LEFT JOIN #__rsform_submission_values SV_PrimaryZip
ON SV_Date.SubmissionId = SV_PrimaryZip.SubmissionId
LEFT JOIN #__rsform_submission_values SV_Gender
ON SV_Date.SubmissionId = SV_Gender.SubmissionId
LEFT JOIN #__rsform_submission_values SV_Ethnicity
ON SV_Date.SubmissionId = SV_Ethnicity.SubmissionId
LEFT JOIN #__rsform_submission_values SV_CommunityRelations
ON SV_Date.SubmissionId = SV_CommunityRelations.SubmissionId
LEFT JOIN #__rsform_submission_values SV_GroupCounseling
ON SV_Date.SubmissionId = SV_GroupCounseling.SubmissionId
LEFT JOIN #__rsform_submission_values SV_FamilyCounseling
ON SV_Date.SubmissionId = SV_FamilyCounseling.SubmissionId
LEFT JOIN #__rsform_submission_values SV_AddictionsCounseling
ON SV_Date.SubmissionId = SV_AddictionsCounseling.SubmissionId
LEFT JOIN #__rsform_submission_values SV_Accident
ON SV_Date.SubmissionId = SV_Accident.SubmissionId
LEFT JOIN #__rsform_submission_values SV_Death
ON SV_Date.SubmissionId = SV_Death.SubmissionId
LEFT JOIN #__rsform_submission_values SV_EmotionalProblem
ON SV_Date.SubmissionId = SV_EmotionalProblem.SubmissionId
LEFT JOIN #__rsform_submission_values SV_FamilyStress
ON SV_Date.SubmissionId = SV_FamilyStress.SubmissionId
LEFT JOIN #__rsform_submission_values SV_ViolentCrime
ON SV_Date.SubmissionId = SV_ViolentCrime.SubmissionId
LEFT JOIN #__rsform_submission_values SV_OfficerSupport
ON SV_Date.SubmissionId = SV_OfficerSupport.SubmissionId
LEFT JOIN #__rsform_submission_values SV_Synopsis
ON SV_Date.SubmissionId = SV_Synopsis.SubmissionId
LEFT JOIN #__rsform_submission_values SV_GeneratedCaseNumber
ON SV_Date.SubmissionId = SV_GeneratedCaseNumber.SubmissionId
WHERE
SV_Date.FieldName = 'Date'
AND
SV_FirstName.FieldName = 'First Name'
AND
SV_LastName.FieldName = 'Last Name'
AND
SV_EventNumber.FieldName = 'Event Number'
AND
SV_CaseNumber.FieldName = 'Case Number'
AND
SV_DutyStatus.FieldName = 'Duty Status'
AND
SV_Followup.FieldName = 'Followup'
AND
SV_TimeReceived.FieldName = 'Time Received'
AND
SV_TimeArrived.FieldName = 'Time Arrived'
AND
SV_TimeCleared.FieldName = 'Time Cleared'
AND
SV_TotalTimeOnCall.FieldName = 'Total Time On Call'
AND
SV_PrimaryOfficersDetail.FieldName = 'Primary Officers Detail'
AND
SV_StartMileage.FieldName = 'Start Mileage'
AND
SV_EndMileage.FieldName = 'End Mileage'
AND
SV_AreaServed.FieldName = 'Area Served'
AND
SV_TotalServed.FieldName = 'Total Served'
AND
SV_DispatchedAddress.FieldName = 'Dispatched Address'
AND
SV_DispatchedZip.FieldName = 'Dispatched Zip'
AND
SV_PrimaryContact.FieldName = 'Primary Contact'
AND
SV_PrimaryContactPhone.FieldName = 'Primary Contact Phone'
AND
SV_PrimaryAddress.FieldName = 'Primary Address'
AND
SV_PrimaryZip.FieldName = 'Primary Zip'
AND
SV_Gender.FieldName = 'Gender'
AND
SV_Ethnicity.FieldName = 'Ethnicity'
AND
SV_CommunityRelations.FieldName = 'Community Relations'
AND
SV_GroupCounseling.FieldName = 'Group Counseling'
AND
SV_FamilyCounseling.FieldName = 'Family Counseling'
AND
SV_AddictionsCounseling.FieldName = 'Addictions Counseling'
AND
SV_Accident.FieldName = 'Accident'
AND
SV_Death.FieldName = 'Death'
AND
SV_EmotionalProblem.FieldName = 'Emotional Problem'
AND
SV_FamilyStress.FieldName = 'Family Stress'
AND
SV_ViolentCrime.FieldName = 'Violent Crime'
AND
SV_OfficerSupport.FieldName = 'Officer Support'
AND
SV_Synopsis.FieldName = 'Synopsis'
AND
SV_GeneratedCaseNumber.FieldName = 'Generated Case Number'
GROUP BY
SV_Date.SubmissionId
|
|
|
|
|
|
|
|
Re:SQL Query getting form ID, but not submitted data 12 Years, 1 Month ago
|
Karma: 748
|
|
The query can be slow because a lot of "JOIN" are used, but it requires due to structure of your table.
Regards,
ARI Soft
|
|
|
|
|
|
|
