I have 3 tables, 1 where voters information is stored, 2 where their address is stored and 3 where voter ids are matched with address ids. I thought this approach was best as several voters can occupy the same address. Now I have Query that selects all the voters on a given street / road. Although it works, I'm not sure if I should be using joins or keep using where, and if locate is the best way to search a field.SELECT `voter_name`, `address_full`FROM `tr_voter`, `tr_voter_address`, `tr_voter_address_assigned`WHERE tr_voter_address_assigned.voter_id = tr_voter.voter_id AND tr_voter_address_assigned.address_id = tr_voter_address.address_id AND LOCATE('insert street name here', `address_full`) ORDER BY `address_2`, `address_1`LIMIT 0 , 200address_full is the full address, where each component is broken down into address_1, address_2 etc. This does mean however, in some cases with house names, that a street may be stored in 1 2 or 3, so searching the full field seemed the full proof way to do it.Voter Table`voter_id` int(11) NOT NULL AUTO_INCREMENT,`voter_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,`voter_number` varchar(11) COLLATE utf8_unicode_ci NOT NULL, `voter_title` varchar(20) COLLATE utf8_unicode_ci NOT NULL,`voter_first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,`voter_last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,`voter_notes` text COLLATE utf8_unicode_ci NOT NULL,`voter_last_vote_date` datetime DEFAULT NULL,`voter_entry_date` datetime NOT NULL,`voter_last_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (`voter_id`),UNIQUE KEY `number` (`voter_number`)Address Table`address_id` int(11) NOT NULL AUTO_INCREMENT,`address_full` varchar(255) COLLATE utf8_unicode_ci NOT NULL,`address_1` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'address name or number',`address_2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,`address_3` varchar(255) COLLATE utf8_unicode_ci NOT NULL,`address_4` varchar(255) COLLATE utf8_unicode_ci NOT NULL,`address_5` varchar(255) COLLATE utf8_unicode_ci NOT NULL,`address_6` varchar(255) COLLATE utf8_unicode_ci NOT NULL,`address_lat` varchar(255) COLLATE utf8_unicode_ci NOT NULL,`address_long` varchar(255) COLLATE utf8_unicode_ci NOT NULL,PRIMARY KEY (`address_id`),UNIQUE KEY `address_full` (`address_full`)Assigned Table`assigned_id` int(11) NOT NULL AUTO_INCREMENT,`voter_id` int(11) NOT NULL,`address_id` int(11) NOT NULL,PRIMARY KEY (`assigned_id`)
Searching for voters by address
performance;sql;mysql;search
OK Assuming you need the Assigned Table to support the many to many relationship of a voter to address.`assigned_id` int(11) NOT NULL AUTO_INCREMENT,`voter_id` int(11) NOT NULL,`address_id` int(11) NOT NULL,PRIMARY KEY (`assigned_id`)You don't need assigned_id. Remove this. The primary key is (voter_id, address_id)