aethelwulffe wrote on Thursday, August 09, 2012:
OK, here is a “I only speak 5 words of MYSQL syntax” Query that does the following:
Looks in table ‘forms’ for date range rows for a LBF form called FARS/CFARS;
Draws up the Patient insurance data relative to the report being run;
Adds each field in the FARS/CFARS form layout as a join from lbf_data (there are a lot of them) and adds them to a horizontal row from the vertical table.
SELECT DISTINCT T1.`id` , T1.`date` , T1.`encounter` , T1.`form_name` , T1.`form_id` , T1.`pid` ,
T2.`form_id`,
field1.`field_value` ,
field2.`field_value` ,
field3.`field_value` ,
field4.`field_value` ,
field5.`field_value` ,
field6.`field_value` ,
field7.`field_value` ,
field8.`field_value` ,
field9.`field_value` ,
field10.`field_value` ,
field11.`field_value` ,
field12.`field_value` ,
field13.`field_value` ,
field14.`field_value` ,
field15.`field_value` ,
field16.`field_value` ,
field17.`field_value` ,
field18.`field_value` ,
field19.`field_value` ,
field20.`field_value` ,
field21.`field_value` ,
field22.`field_value` ,
field23.`field_value` ,
field24.`field_value` ,
field25.`field_value` ,
field26.`field_value` ,
field27.`field_value` ,
field28.`field_value` ,
T3.`policy_number` , T3.`subscriber_DOB` , T3.`subscriber_fname` , T3.`subscriber_lname` , T3.`pid`
FROM `forms` AS T1
JOIN `lbf_data` AS T2
JOIN `lbf_data` AS field1
JOIN `lbf_data` AS field2
JOIN `lbf_data` AS field3
JOIN `lbf_data` AS field4
JOIN `lbf_data` AS field5
JOIN `lbf_data` AS field6
JOIN `lbf_data` AS field7
JOIN `lbf_data` AS field8
JOIN `lbf_data` AS field9
JOIN `lbf_data` AS field10
JOIN `lbf_data` AS field11
JOIN `lbf_data` AS field12
JOIN `lbf_data` AS field13
JOIN `lbf_data` AS field14
JOIN `lbf_data` AS field15
JOIN `lbf_data` AS field16
JOIN `lbf_data` AS field17
JOIN `lbf_data` AS field18
JOIN `lbf_data` AS field19
JOIN `lbf_data` AS field20
JOIN `lbf_data` AS field21
JOIN `lbf_data` AS field22
JOIN `lbf_data` AS field23
JOIN `lbf_data` AS field24
JOIN `lbf_data` AS field25
JOIN `lbf_data` AS field26
JOIN `lbf_data` AS field27
JOIN `lbf_data` AS field28
JOIN `insurance_data` AS T3
WHERE T1.`date` > '2012-07-01 00:30:00'
AND T1.`date` < '2012-08-01 00:30:00'
AND T1.`form_name` LIKE 'FARS/CFARS'
AND T1.`deleted` =0
AND T2.`form_id` = T1.`form_id`
AND T1.`pid` = T3.`pid`
AND T3.`type` = 'primary'
AND field1.`form_id` = T2.`form_id`
AND field1.`field_id` = 'farscfars'
AND field2.`form_id` = T2.`form_id`
AND field2.`field_id` = 'ethnicitude'
AND field3.`form_id` = T2.`form_id`
AND field3.`field_id` = 'abusehx'
AND field4.`form_id` = T2.`form_id`
AND field4.`field_id` = 'assessmentpurp'
AND field5.`form_id` = T2.`form_id`
AND field5.`field_id` = 'evaldate'
AND field6.`form_id` = T2.`form_id`
AND field6.`field_id` = 'edumucashun'
AND field7.`form_id` = T2.`form_id`
AND field7.`field_id` = 'cfarnum'
AND field8.`form_id` = T2.`form_id`
AND field8.`field_id` = 'evalname'
AND field9.`form_id` = T2.`form_id`
AND field9.`field_id` = 'provmedi'
AND field10.`form_id` = T2.`form_id`
AND field10.`field_id` = 'countyserv'
AND field11.`form_id` = T2.`form_id`
AND field11.`field_id` = 'xxdep'
AND field12.`form_id` = T2.`form_id`
AND field12.`field_id` = 'xxanx'
AND field13.`form_id` = T2.`form_id`
AND field13.`field_id` = 'xxhyp'
AND field14.`form_id` = T2.`form_id`
AND field14.`field_id` = 'xxtho'
AND field15.`form_id` = T2.`form_id`
AND field15.`field_id` = 'xxcog'
AND field16.`form_id` = T2.`form_id`
AND field16.`field_id` = 'xxmed'
AND field17.`form_id` = T2.`form_id`
AND field17.`field_id` = 'xxtra'
AND field18.`form_id` = T2.`form_id`
AND field18.`field_id` = 'xxpot'
AND field19.`form_id` = T2.`form_id`
AND field19.`field_id` = 'xxint'
AND field20.`form_id` = T2.`form_id`
AND field20.`field_id` = 'xxhome'
AND field21.`form_id` = T2.`form_id`
AND field21.`field_id` = 'xxfam'
AND field22.`form_id` = T2.`form_id`
AND field22.`field_id` = 'xxadl'
AND field23.`form_id` = T2.`form_id`
AND field23.`field_id` = 'xxsoc'
AND field24.`form_id` = T2.`form_id`
AND field24.`field_id` = 'xxwor'
AND field25.`form_id` = T2.`form_id`
AND field25.`field_id` = 'xxcare'
AND field26.`form_id` = T2.`form_id`
AND field26.`field_id` = 'xxdan'
AND field27.`form_id` = T2.`form_id`
AND field27.`field_id` = 'xxdto'
AND field28.`form_id` = T2.`form_id`
AND field28.`field_id` = 'xxsec'
PROBLEM(s):
1. This query just looks wrong. Too many repeated operations. Got to be a better way.
2. Without using the DISTINCT function, I get endless seeming duplicate rows, presumably every possible permutation, or perhaps infinite (thank goodness for the LIMIT function when testing).
3. The Killer: How to do an IF or while statement in MYSQL for when you have optional fields? The normalized table does not use NULL or default rows! For instance field21 and field25 look for a field_id of xxfam and xxcare respectively, and that will not exist in all forms. I can find some kinda unrelated examples, but I would like to hear a suggestion that take into consideration the query sequence to ensure there is a value to bounce an IF off of, not to mention and optimization of the query method itself…before I add a few hundred more lines to this query.
Folks speak regularly about keeping the processing at the database level, so I sure would not mind learning how to do this properly.