Search multiple columns in two mysql tables?

Question

I have two tables and a search form to just search for a keyword. I am trying to search for that keyword on two table for multiple columns and if the query matches get the id column for further use. I have tried this (suppose "coupon" is the term user is searching for)

SELECT `ID` FROM `Profiles` AS `p` WHERE `p`.`Status` = 'Active' AND `p`.`Address`
LIKE '%coupon%' OR `p`.`BusinessName` LIKE '%coupon%' OR `p`.`BusinessSubCategory`
LIKE '%coupon%' OR `p`.`DescriptionMe` LIKE '%coupon%' OR `p`.`Tags` LIKE '%coupon%'
UNION SELECT `id` FROM `products` AS `d` WHERE `d`.`status` = 'approved' AND
`d`.`title` LIKE '%coupon%' OR `d`.`desc` LIKE '%coupon%' OR `d`.`tags` LIKE '%coupon%'

Here i want the id of profile and id of products that matches the keyword. I tried this and this is returning very strange results and looks like only profile ID. So, its a wrong query. What should be the query for this kind of search? INNER JOIN? Please give me some sample queries for this, i will be very grateful for any help.

NewInTheBusiness · Accepted Answer · 2012-07-15 12:53:47Z

2

First off, I wouldn't use AS p when you're not doing an INNER JOIN etc... seems like overdoing it plus I guess you need parentheses after AND - surrounding the ORs as well if you explicity want the to find results where status is "Active".

How about:

    SELECT ID FROM Profiles WHERE Status = 'Active' AND (Address LIKE '%coupon%' OR BusinessName LIKE '%coupon%' OR BusinessSubCategory LIKE '%coupon%' OR DescriptionMe LIKE '%coupon%' OR Tags LIKE '%coupon%')
UNION SELECT id FROM products WHERE status = 'approved' AND (title LIKE '%coupon%' OR desc LIKE '%coupon%' OR tags LIKE '%coupon%')

edited Jul 15, 2012 at 12:53

answered Jul 15, 2012 at 12:29

NewInTheBusiness

1,4501 gold badge9 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Prashank Over a year ago

I was changing the query repeatedly, i was trying INNER JOIN first and after that tried UNION so, i thought AS p or AS d will not affect the result.

NewInTheBusiness Over a year ago

The parentheses around the ORs though can make the difference depending on precisely what kind of result you want

Prashank Over a year ago

syntax error near 'desc LIKE '%coupon%' OR tags LIKE '%coupon%') LIMIT 0, 30' at line 2

ypercubeᵀᴹ Over a year ago

@Prashank: DESC should have backquotes around it: `DESC` Or (even better) change this column name to a non-reserved word.

Prashank Over a year ago

Same thing its returning correct id of the product but on the column name of "ID"

|

Sashi Kant · Accepted Answer · 2012-07-15 12:58:45Z

1

Try this::

SELECT `ID`,'profile_ID' FROM 
`Profiles` AS `p` 
WHERE `p`.`Status` = 'Active' AND `p`.`Address`
LIKE '%coupon%' OR `p`.`BusinessName` LIKE '%coupon%' OR `p`.`BusinessSubCategory`
LIKE '%coupon%' OR `p`.`DescriptionMe` LIKE '%coupon%' OR `p`.`Tags` LIKE '%coupon%'


UNION ALL

SELECT `id`, 'productID' FROM `products` AS `d` WHERE `d`.`status` = 'approved' AND
`d`.`title` LIKE '%coupon%' OR `d`.`desc` LIKE '%coupon%' OR `d`.`tags` LIKE '%coupon%'

edited Jul 15, 2012 at 12:58

answered Jul 15, 2012 at 12:25

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

14 Comments

Prashank Over a year ago

Its returning nothing, as i can see there is coupon keyword in two products's desc column. So, at least two products should return but nothing :(

Sashi Kant Over a year ago

@what is the status there then??

Prashank Over a year ago

all products status are approved.

Prashank Over a year ago

Okay now i know whats happening. Your new query is returning same thing as mine but first i thought its returning 2 from profiles but its returning any result on column name 'ID' but i was expecting results from products table on name 'id' and from profiles table on name 'ID' but thats not happening. :(

Sashi Kant Over a year ago

You did UNION in which if the profileID and product Id is same, you will get only 1 row, but in UNION ALL it will return 2 rows

|

Collectives™ on Stack Overflow

Search multiple columns in two mysql tables?

2 Answers 2

6 Comments

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related