HI,
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.
Steve
I am definitely learning by “Fire and Brimstone” 
