Log in to AnswerLog in to Follow
Rob Marshall
25Views
9Comments
Check multiple table fields for a corresponding value
Question
SQL
Aggregate
Traditional Web
Application Type
Traditional Web

I have a search function in my web app which attempts to match the input values to one of many values across multiple tables. The search inputs are "Company", "Contact", "Address"(street, town/city, postcode), phone. Everything is working fine until the user enters a phone number. The problem is the phone number could exist in any one of 8 places. For example the table holding companies (contact_entities) has 2 possible matching fields, contact_people has 4 possible matching fields and contact_address has another 2 possible matching fields. I am using the filter below to try get a match but this consistently fails and produces 0 results even when I know there is a match.

For example if I searched:

Name: "exampleName", Street:"exampleStreet", Town: "exampleTown"

 and get a result and then I search

Name: "exampleName", Street:"exampleStreet", Town: "exampleTown", Phone: "000000000" 

and know the number to be correct the search fails and produces 0 results.

Phone1 = ""

or contact_address.auto_business_phone_1 like "%" + Phone1 + "%"

or contact_address.auto_business_phone_2 like "%" + Phone1 + "%"

or contact_people.auto_phone_work_1 like "%" + Phone1 + "%"

or contact_people.auto_phone_work_2 like "%" + Phone1 + "%"

or contact_people.auto_phone_personal_1 like "%" + Phone1 + "%"

or contact_people.auto_phone_personal_2 like "%" + Phone1 + "%"

or contact_entities.auto_business_phone_1 like "%" + Phone1 + "%"

or contact_entities.auto_business_phone_2 like "%" + Phone1 + "%"


Can anybody suggest a better way to structure this query?


Thanks

0
0
on 31th Mar
João Marques
mvp_badge
MVP

Hi Rob,


Which is the type of the attributes you're storing the phone numbers? Are they numbers?

It seems to me, especially given your example with 00000, that the phone numbers are being stored as numbers (which would mean they are stored as 0, not as 00000, hence the failed result) or they are being considered as numbers when you apply the query.


Kind Regards,
João

0
0
on 31th Mar

Hi João,


Thanks for the quick reply. In answer to your question the phone numbers are being stored as 'varchar' in the database and the variable which holds the value Phone1 is that of text so it is not a datatype issue. I will amend my example to make this clear.

0
0
on 31th Mar

Hi Rob, 

I am just trying to think with you but try to check joins between tables. You can comment all conditions and joins except ones related to phone number. 

0
0
on 31th Mar

Hi Rob,

I would suggest using "UNION ALL" in an Advance Query if it is NOT possible to join the Entities or share the relationships between the entities. Are they children of a Parent entity?

-cezarF



0
0
on 1st Apr

I have attached images of the joins and filters I have in place (with the phone filter omitted for now).


The contact_entities table holds "companies" a company can have many addresses and an address can have many people so the address table is joined to companies(contact_entities) by holding a contact_entity_id and the contact_people are joined to addresses by holding a contact_address_id.

The search function is designed to be used by anybody in our organisation. Some may be more familiar with addresses others may know the people they are looking up by name others may have a "call history" in front of them so would find it necessary to search the number only to see who/where or which company the call came from etc etc.

As you can see it is possible to join all the tables in an aggregate and for the most part all the search functions work as expected except when the system fills the search conditions automatically from a 'snapshot'. When it does this it fills all search conditions if they are available (which is something a human would not typically do) and runs the search automatically, typically turning up 0 results until the user omits the phone number, then it will return the desired result including the phone number. This is undesirable as the users are encouraged to enter the person/company/address into the database if it does not exist in our data already and asking them to omit the phone number to find out is not acceptable.

I am already using funnels to try and slim down the queries dependent on which inputs have been submitted  as you can see in this image but there must be the ability to return the correct results when all filters are populated.

Any more suggestions are much appreciated.

Thanks

0
0
on 1st Apr

Rob, if you don't mind me saying so, I think you should go back to the drawing board to design a better DB.

The way I see, you will be in trouble when the number of records starts to grow so will grow the response time to the user, more records more time more dissatisfaction  ....

Relational DB's don't like relations, what I mean is that the more relations you build, the worst will be to build the statements and the response time , that's is  me thinking out loud. 

I'm sure that there will a better design and you will achieve your objectives.

Regards

0
0
on 3rd Apr

Hi Carl,

Thanks for your suggestions and apologies in taking so long to send you a reply. I have been on a weeks break.

Unfortunately the DB is one that has been designed many years ago and has been in place with my organization for a long time. They have many other applications using the DB as it is so I have no power to make any improvements or changes. Basically I need to work with what I have been given.

0
0
8 days ago

Hi Rob,

I would like to encourage you to keep your filters simple. It makes it easier to catch the mistake.
For example, I saw if-statements in your filters, while the filters itself are basically if-statements.

I think the first one can be rewritten as:
deleted = True or contact_people.deleted = NullDate() or contact_address.deleted = NullDate() or contact_entities.deleted = NullDate()

The second one would be:
docStatus = 0 or contractors_insurance.correct_documents = docStatus

Regards,
Lennart

0
0
on 5th Apr

Hi Lennart,

Thank you for your reply, I will take this on board and try it out.

Thanks 

Rob

0
0
8 days ago
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.