CODEWITHSUNDEEP
sqlmysqldatabase
Teej
Teej

Reputation: 12873

Is there a way to search 2 or more fields at the same time?

I am looking into creating something that does this:

SELECT *
FROM (`tm_accounts`)
WHERE  CONCAT(`last_name`, `first_name`)  LIKE '%o%'

This, of course, doesn't work. I just want you to see the idea behind what I'm trying to emulate.

last_name and first_name are two separate fields in the accounts table

Upvotes: 2

Views: 267

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332531

I recommend using:

SELECT a.*
  FROM TM_ACCOUNTS a
 WHERE a.first_name LIKE '%o%'
UNION
SELECT b.*
  FROM TM_ACCOUNTS b
 WHERE b.last_name LIKE '%o%'

ORs are notorious for poor performance, and have maintenance risks.

Mind that I'm using UNION, which will remove duplicates from the ultimate resultset. While UNION ALL would perform better, it does not remove duplicates - making it not ideal for use in this case.

In case you weren't aware, putting a wildcard on the lefthand side of your LIKE criteria won't use an index. So if you had an index on TM_ACCOUNTS.last_name or TM_ACCOUNTS.first_name (or both if using a covering index).

This:

WHERE CONCAT(`last_name`, `first_name`)  LIKE '%o%'

...isn't a good habit - a better approach is to perform the function in a subselect/inline view. For example:

SELECT x.*
  FROM (SELECT t.*,
               CONCAT(t.last_name, t.first_name) AS full_name
          FROM TM_ACCOUNTS t) x
 WHERE x.full_name LIKE '%o%'

Upvotes: 9

Jim
Jim

Reputation: 1624

SELECT *
FROM (`tm_accounts`)
WHERE last_name LIKE '%o%' 
   or first_name LIKE '%o%';

Upvotes: 9

Related Questions