I’m facing a tough conceptual issue regarding a parameterized search in the database. Parameterized search is where a user can from dropdowns or free text fields search on things like phone number, country, market…
Here is the SELECT query for this so far:
SELECT l.lead_id as 'Registrant Number' , l.salutation as 'Salutation' , l.first_name as 'First Name' , l.middle_names as 'Middle Names' , l.last_name as 'Last Name' , l.language as 'Preferred Lanaguage' , l.written_greeting as 'Written Greeting' , l.verbal_greeting as 'Verbal Greeting' , l.degree as 'Degree' , l.date_entered as 'Date Entered' , l.date_registered as 'Date Registered' , l.date_modified as 'Date Modified' , ls.lead_source as 'Lead Source' , u.uid as 'Counselor' , m.market_name as 'Market' , lc.location_name as 'Location' , sta.name as 'Status Name' , l.appointment_set as 'Appointment Set' , l.appointment_details as 'Appointment Details' , ss.sale_status as 'Sales Type' , l.follow_up_details as 'Follow Up Details' , g.gender as 'Gender' , y.year as 'Year of Birth' , ms.marital_status_name as 'Marital Status' , la.language as 'Language' , l.do_not_call as "Don't Call" , l.do_not_email as "Don't Email" , l.do_not_mail as "Don't Mail" , l.special_instructions as 'Special Instructions' , l.notes as 'Notes' , v.veteran_status as 'Veteran Status' , l.is_deceased as 'Is Deceased' FROM leads as l INNER JOIN lead_sources as ls ON l.lead_source_id = ls.lead_source_id INNER JOIN users as u ON l.counselor_id = u.uid_number INNER JOIN markets as m ON l.market_id = m.market_id INNER JOIN locations as lc ON l.location_id = lc.location_id INNER JOIN lk_genders as g ON l.gender_id = g.gender_id LEFT OUTER JOIN years as y ON l.year_of_birth_id = y.year_id INNER JOIN marital_status as ms ON l.married_status_id = ms.marital_status_id INNER JOIN lk_languages as la ON l.language_id = la.language_id LEFT OUTER JOIN lk_veterans as v ON l.veteran_status_id = v.veterans_id LEFT OUTER JOIN leads2addresses as l2a ON l.lead_id = l2a.lead_id LEFT OUTER JOIN addresses as a ON l2a.address_id = a.address_id LEFT OUTER JOIN postal_codes as p ON a.post_code = p.postal_code LEFT OUTER JOIN countries as cnt ON p.country_iso_code = cnt.country_iso_code INNER JOIN sales_status as ss ON l.sales_type_id = ss.sale_status_id LEFT OUTER JOIN statuses as sta ON l.status_id = sta.status_id LEFT OUTER JOIN provs_states as pst ON p.state_prov_abbr = pst.abbreviation AND a.post_code = p.postal_code LEFT OUTER JOIN leads2communications as l2c ON l.lead_id = l2c.lead_id LEFT OUTER JOIN communications as com ON l2c.communication_id = com.communication_id WHERE 1 = 1
I can tag AND statments onto the end to search for specifics.
A lead can have many different types of phone numbers Work, Home, Cell … which is why the leads2communication table exists
delimiter $$ CREATE TABLE `leads2communications` ( `lead_id` int(11) NOT NULL, `communication_id` int(11) NOT NULL, PRIMARY KEY USING BTREE (`lead_id`,`communication_id`), CONSTRAINT `fk_leads2communications_leads` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`lead_id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$
The conceptual issue that I face is driven by this part of the above SELECT statement
LEFT OUTER JOIN leads2communications as l2c ON l.lead_id = l2c.lead_id LEFT OUTER JOIN communications as com ON l2c.communication_id = com.communication_id
If a user searches by phone number then I can use the following to return that unique record (bogus phone number):
AND com.phone_number = '905-000-0000'
However if I don’t append the above ‘AND’ filter then I get the same lead duplicated for every phone number linked to that particular lead, which is what is expected given the query.
Now if in the SELECT query I use the following
AND com.is_primary_phone_number = 1
then I will get the single record for that lead using no ‘AND’ filters after the ‘WHERE 1 = 1’. But this is not good as a user should be able to search on any of the leads phone numbers and by imposing this particular ‘AND’ filter that is not possible.
The communication table is currently
delimiter $$ CREATE TABLE `communications` ( `communication_id` int(11) NOT NULL auto_increment, `communication_type_id` tinyint(4) NOT NULL default '0', `phone_number` varchar(50) default NULL, `internal_extension` smallint(6) default NULL, `secondary_dialing_instructions` text, `complete_foreign_dialing_sequence` text, `complete_local_dialing_sequence` text, `text_handle` varchar(250) default NULL, `email_address` varchar(250) default NULL, `url` varchar(250) default NULL, PRIMARY KEY USING BTREE (`communication_id`), KEY `FK_index` (`communication_type_id`) ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8$$
Is there a way to use conditional logic so that in the WHERE 1=1 without any phone number specified in the search used without “AND com.phone_number = ‘905-000-0000’” a single lead is returned; however when a phone number is specified then return the lead with that specific phone number?
Hopefully this makes sense.