Avatar of tim_carter
tim_carter asked on

How to make a like search in multiple tables

Hi guys

im a little stuck here and hope someone can help me, im trying to make a LIKE query that look in multiple tables to find some data. Here is the tricky part, even though i want to look in different tables, i only want data from the tables that actually have some data that compares to what the user search for.

I have made this query


SELECT
                  Customers.CustomerId,
                  ParentCompanyNames.CompanyName as ParentCompanyName,
                  Customers.CompanyName,
                  CustomerAssociates.Name,
                  PMBNumbers.PMBNumber,
                  BoxNumbers.BoxNumber,
                  CustomerHandling.MailHandlingScan,
                  CustomerHandling.MailHandlingEmail,
                  CustomerHandling.MailHandlingFax,
                  CustomerHandling.MailHandlingCall,
                  CustomerHandling.MailHandlingSpecial
            FROM
                  Customers
            LEFT JOIN ParentCompanyNames ON Customers.CustomerId=ParentCompanyNames.CustomerId
            LEFT JOIN CustomerAssociates ON Customers.CustomerId=CustomerAssociates.CustomerId
            LEFT JOIN PMBNumbers ON Customers.CustomerId=PMBNumbers.CustomerId
            LEFT JOIN BoxNumbers ON Customers.CustomerId=BoxNumbers.CustomerId
            LEFT JOIN CustomerHandling ON Customers.CustomerId=CustomerHandling.CustomerId
            WHERE
                  Customers.BusinessCenterId = 4
                  AND (Customers.CompanyName like '%test%' OR ParentCompanyNames.CompanyName like '%test%' OR CustomerAssociates.Name like '%test%')
            ORDER BY
                  ParentCompanyName,Name      

But ofcourse this will return rows from all the tables if there is data in them, how do i get around that? Hope you can understand what i mean. Thank you
SQL

Avatar of undefined
Last Comment
tim_carter

8/22/2022 - Mon
Mark Wills

How is the user's choice able to be passed to the query ? is it a stored procedure or are you generating the query ?  Think you need to achieve the following:


    AND ((Customers.CompanyName like '%test%' and @users_choice = 'Customers')
            OR (ParentCompanyNames.CompanyName like '%test%' and @users_choice = 'Parent')
            OR (CustomerAssociates.Name like '%test%' and @users_choice = 'Associates'))


is that possible ? the challenge is to 'know' the users choice...



ASKER
tim_carter

Well i know i could do it that way, with a selection. where the user selects what to search for, but i wanted to make it work without the user actually having to do anything else, but just enter what to search. But i dont know if this is  possibly? .. No it is not a stored procedure. Im just executing this through PHP. Thanks
Guy Hengel [angelIII / a3]

>Here is the tricky part, even though i want to look in different tables, i only want data from the tables that actually have some data that compares to what the user search for

can you clarify that in detail, please?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Mark Wills



OK, think I understand... What about if...

AND 1 = (Case when Customers.CompanyName like '%test%' then 1
                         when ParentCompanyNames.CompanyName like '%test%' then 1
                         when CustomerAssociates.Name like '%test%' then 1
                         else 0
                end)

and it might also be worthwhile to add the keyword DISTINCT to the select ie SELECT DISTINCT ...
ASKER
tim_carter

ok lets say im entering this phrase to search after. MICHAEL. then lets say that a person name michael exist in the table CustomerAssociates.

My problem is that if MICHAEL exist in CustomerAssociates. then if the CustomerId, appears Twice in lets say ParentCompanyNames because the customer had to parent names, then Michael will Appear Twice in my select, because it will ofcourse return the Rows found in ParentCompanyNames aswell. Is there away around that? Or will i have to do it the old fashioned way and making the user select what table they actually wants their results from?

Thanks guys
ASKER CERTIFIED SOLUTION
Mark Wills

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
tim_carter

Thank you mark.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.