Skip to content

Instantly share code, notes, and snippets.

@jamiejackson

jamiejackson/1_query.sql Secret

Last active Sep 11, 2015
Embed
What would you like to do?
SELECT
`wp`.`id` AS `wpID`,
`recipient`.`id` AS `recipientid`,
`recipient`.`recipientTypeID` AS `recipientTypeID`,
`recipient`.`sortorder` AS `sortorder`,
(CASE
WHEN
((`cto`.`orgname` IS NOT NULL)
AND (`recipient`.`recipientTypeID` = 4))
THEN
1
WHEN
((`cto`.`orgname` IS NOT NULL)
AND (`recipient`.`recipientTypeID` = 7))
THEN
3
ELSE 2
END) AS `recipientTypeSort`,
(CASE
WHEN (`cto`.`orgname` IS NOT NULL) THEN CONVERT( CAST(`recipient`.`granteeid` AS CHAR CHARSET LATIN1) USING UTF8)
ELSE `ctc`.`coc_no`
END) AS `granteeid`,
(CASE
WHEN (`cto`.`orgname` IS NOT NULL) THEN `cto`.`grantee_state`
ELSE `ctc`.`state_id`
END) AS `granteestate`,
(CASE
WHEN (`cto`.`orgname` IS NOT NULL) THEN `cto`.`orgname`
ELSE CONCAT(`ctc`.`coc_no`, ' - ', `ctc`.`coc_name`)
END) AS `granteename`,
`fo`.`orgname` AS `fieldOffice`,
(CASE
WHEN
(`cto`.`orgname` IS NOT NULL)
THEN
CONCAT(IFNULL(`cto`.`orgname`, ''),
' - ',
IFNULL(`fo`.`orgname`, ''))
WHEN
(`ctc`.`coc_no` IS NOT NULL)
THEN
CONCAT(IFNULL(CONCAT(`ctc`.`coc_no`, ' - ', `ctc`.`coc_name`),
''),
' - ',
IFNULL(`fo`.`orgname`, ''))
ELSE NULL
END) AS `granteeFieldOffice`
FROM
((((((`tap_tbl_workplan` `wp`
LEFT JOIN `tap_asc_oncallwp_assignment` `oncall` ON (((`oncall`.`workplanid` = `wp`.`id`)
AND (`oncall`.`association_completed` = 1))))
LEFT JOIN `tap_tbl_assignment` `assign` ON (((`assign`.`id` = `wp`.`assignmentid`)
OR (`assign`.`id` = `oncall`.`assignmentid`))))
LEFT JOIN `tap_tbl_recipient` `recipient` ON (((`recipient`.`requestid` = `assign`.`requestid`)
OR (`recipient`.`workplanid` = `wp`.`id`))))
LEFT JOIN `c_torganizations` `fo` ON ((`fo`.`orgid` = `recipient`.`fieldofficeid`)))
LEFT JOIN `c_torganizations` `cto` ON ((`cto`.`orgid` = `recipient`.`granteeid`)))
LEFT JOIN `c_tcoc` `ctc` ON ((`ctc`.`coc_no` = `recipient`.`coc_no`)))
ORDER BY
`wp`.`id` ,
(CASE
WHEN
((`cto`.`orgname` IS NOT NULL)
AND (`recipient`.`recipientTypeID` = 4))
THEN
1
WHEN
((`cto`.`orgname` IS NOT NULL)
AND (`recipient`.`recipientTypeID` = 7))
THEN
3
ELSE 2
END) ,
`recipient`.`recipientTypeID` , `recipient`.`sortorder` , `recipient`.`id`
+------+-------------+-----------+--------+----------------------------------------------------------+---------------------------------+---------+-------------------------------------------+------+--------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+--------+----------------------------------------------------------+---------------------------------+---------+-------------------------------------------+------+--------------------------------------------------+
| 1 | SIMPLE | wp | index | NULL | fk_tap_tbl_workplan1 | 5 | NULL | 2414 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | oncall | ref | fk_tap_asc_oncallwp_assignment2 | fk_tap_asc_oncallwp_assignment2 | 4 | hudexchange_test2.wp.id | 3 | Using where |
| 1 | SIMPLE | assign | ALL | PRIMARY | NULL | NULL | NULL | 537 | Range checked for each record (index map: 0x1) |
| 1 | SIMPLE | recipient | ALL | fk_tap_tbl_recipient1,fk_tap_tbl_recipient5,JAMIE,jamie2 | NULL | NULL | NULL | 5890 | Range checked for each record (index map: 0x2A2) |
| 1 | SIMPLE | fo | eq_ref | PRIMARY | PRIMARY | 4 | hudexchange_test2.recipient.fieldofficeid | 1 | Using where |
| 1 | SIMPLE | cto | eq_ref | PRIMARY | PRIMARY | 4 | hudexchange_test2.recipient.granteeid | 1 | Using where |
| 1 | SIMPLE | ctc | eq_ref | PRIMARY | PRIMARY | 30 | hudexchange_test2.recipient.coc_no | 1 | Using where |
+------+-------------+-----------+--------+----------------------------------------------------------+---------------------------------+---------+-------------------------------------------+------+--------------------------------------------------+
+------+-------------+-----------+--------+----------------------------------------------------------+---------------------------------+---------+-------------------------------------------+------+--------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-----------+--------+----------------------------------------------------------+---------------------------------+---------+-------------------------------------------+------+--------------------------------------------------+
| 1 | SIMPLE | wp | index | NULL | fk_tap_tbl_workplan1 | 5 | NULL | 2414 | Using index |
| 1 | SIMPLE | oncall | ref | fk_tap_asc_oncallwp_assignment2 | fk_tap_asc_oncallwp_assignment2 | 4 | hudexchange_test2.wp.id | 3 | Using where |
| 1 | SIMPLE | assign | ALL | PRIMARY | NULL | NULL | NULL | 537 | Range checked for each record (index map: 0x1) |
| 1 | SIMPLE | recipient | ALL | fk_tap_tbl_recipient1,fk_tap_tbl_recipient5,JAMIE,jamie2 | NULL | NULL | NULL | 5890 | Range checked for each record (index map: 0x2A2) |
| 1 | SIMPLE | fo | eq_ref | PRIMARY | PRIMARY | 4 | hudexchange_test2.recipient.fieldofficeid | 1 | Using where |
| 1 | SIMPLE | cto | eq_ref | PRIMARY | PRIMARY | 4 | hudexchange_test2.recipient.granteeid | 1 | Using where |
| 1 | SIMPLE | ctc | eq_ref | PRIMARY | PRIMARY | 30 | hudexchange_test2.recipient.coc_no | 1 | Using where |
+------+-------------+-----------+--------+----------------------------------------------------------+---------------------------------+---------+-------------------------------------------+------+--------------------------------------------------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.