SELECT CONCAT('NRC', `nrc`.`id`) AS `id`,
`nrc`.`name_initials` AS `name_initials`,
`nrc`.`name_suffix` AS `name_suffix`,
`nrc`.`name_last` AS `name_last`,
`nrc`.`sex` AS `sex`,
`location_ptt`.`street_short` AS `street`,
`nrc`.`number` AS `number`,
`nrc`.`number_app` AS `number_app`,
`nrc`.`zipcode` AS `zipcode`,
`nrc`.`city` AS `city`,
`nrc`.`phone` AS `phone`,
`nrc`.`email` AS `email`,
CASE
WHEN `nrc`.`start_date` = `mScoreCard`.`date`
THEN ';;'
ELSE DATE_FORMAT(`nrc`.`start_date`, '%d;%m;%Y')
END AS `start_date`,
`nrc`.`pay_method` AS `pay_method`,
`nrc`.`type` AS `type`,
`nrc`.`account` AS `account`,
DATE_FORMAT(`nrc`.`dob`, '%d;%m;%Y') AS `dob`,
DATE_FORMAT(`mScoreCard`.`date`, '%d-%m-%Y') AS `score_date`,
`mScoreCard`.`route_name` AS `location`
FROM `nrc`
JOIN `location_ptt`
ON `nrc`.`zipcode` = `location_ptt`.`zipcode`
AND `nrc`.`number` BETWEEN `location_ptt`.`number_min` AND `location_ptt`.`number_max`
AND `location_ptt`.`even` = (`nrc`.`number` + 1) MOD 2
JOIN `mScoreCard`
ON `nrc`.`fk_mSCid` = `mScoreCard`.`mSCid`
WHERE `id` = 601;
Explain:
+----+-------------+--------------+-------+--------------------------+---------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+-------+--------------------------+---------+---------+-------+--------+-------------+
| 1 | SIMPLE | nrc | const | PRIMARY,fk_mSCid,zipcode | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | mScoreCard | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 1 | SIMPLE | location_ptt | ref | even | even | 1 | const | 306070 | Using where |
+----+-------------+--------------+-------+--------------------------+---------+---------+-------+--------+-------------+
3 rows in set (0.00 sec)
DESCRIBE nrc;
+---------------+----------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------------------------------------+------+-----+---------+----------------+
| id | int(7) unsigned zerofill | NO | PRI | NULL | auto_increment |
| fk_mSCid | int(11) | NO | MUL | NULL | |
| fk_pid | int(11) | NO | | NULL | |
| sex | enum('M','V','') | NO | | NULL | |
| name_initials | varchar(20) | NO | | NULL | |
| name_suffix | varchar(15) | NO | | NULL | |
| name_last | varchar(40) | NO | | NULL | |
| dob | date | NO | | NULL | |
| number | int(10) unsigned | NO | | NULL | |
| number_app | varchar(7) | NO | | NULL | |
| zipcode | char(6) | NO | MUL | NULL | |
| city | varchar(30) | NO | | NULL | |
| phone | varchar(10) | NO | | NULL | |
| email | varchar(60) | NO | | NULL | |
| account | varchar(9) | NO | | NULL | |
| start_date | date | NO | | NULL | |
| pay_method | tinyint(1) | YES | | NULL | |
| type | enum('W13','W26','W52','M3','M6','Y1') | NO | | NULL | |
| premium | enum('7009') | NO | | NULL | |
| submitted | tinyint(1) | NO | | 0 | |
+---------------+----------------------------------------+------+-----+---------+----------------+
20 rows in set (4.24 sec)
DESCRIBE location_ptt;
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| zipcode | char(6) | NO | MUL | NULL | |
| even | tinyint(3) unsigned | NO | MUL | NULL | |
| number_min | int(10) unsigned | NO | | NULL | |
| number_max | int(10) unsigned | NO | | NULL | |
| fk_cid | int(10) unsigned | NO | MUL | NULL | |
| street_short | varchar(31) | NO | | NULL | |
| street_long | varchar(63) | NO | | NULL | |
| fk_mid | int(10) unsigned | NO | | NULL | |
| fk_pid | tinyint(3) unsigned | NO | | NULL | |
| latitude | float | YES | | NULL | |
| longitude | float | YES | | NULL | |
+--------------+---------------------+------+-----+---------+-------+
11 rows in set (5.72 sec)
|
