Query using LIKE with field names ex. Field1 LIKE Field2

[CLJBville]

I'm pretty new to MYSQL, and SQL in general. I'm having problems with a query and can't seem to find an answer. Here's my query:
SELECT * FROM user_activity INNER JOIN employee USING(user_id) WHERE ac_ptname LIKE lastName

My problem is the WHERE clause. It only returns an exact match. Smith=Smith, but ac_ptname also has a Smith,John that I expected to be returned also, but was not. What am I doing wrong?

The employee table has 649 records, and three fields (user_id,lastName,firstName). The user_activity table has over 300,000 records and quite a few more fields, however the user_id field is the only one they have in common. The ac_ptname field is a text field where the names are typically last only or "Last,First".

 

[towerbase]

You will probably need to use a wildcard such as '%' meaning zero or more characters or '_' meaning a single character.

You will need to concatenate the wild card with your field name. Something like this:

SELECT *
  FROM user
 INNER JOIN employee
      UISNG (user_id)
WHERE ac_ptname LIKE CONCAT(lastname,'%')

Alternatively you might find REGEXP a more suitable alternative to LIKE




Andrew
Hampshire, UK

 

[vacunita]

Why concat, isn't simpler to just do :

WHERE ac_ptname LIKE '%lastName%'

the percent signs is automatically used as wild card no need to concatenate.


----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[r937]

vacunita, no, that doesn't work -- unless you are looking for a person who actually has 'lastName' somewhere in their last name!!!

;-)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[vacunita]

I know you are the SQL guru, here, but it actually works the way its expected.

It looks for anything that has the provided string anywhere in the field.

Example A table with the following in a filed::

id name
------ ------------------
ABC000 Peter Jones
ABC001 Jack Jones Masters
ABC002 Jones Ralph
ABC003 Peter Richardson
ABC004 Richard Sanders
ABC012 Jake Peterson


If I run a query such as:

SELECT *FROM mytable WHERE name LIKE '%Jones%'
it will bring back :
Peter Jones
Jack Jones Masters
Jones Ralph

Why because Jones can be found anywhere in the field.

If I where to do:
SELECT *FROM mytable WHERE name LIKE 'Jones%'

I'd only get back
Jones Ralph. because its the only one that starts with Jones

but ac_ptname also has a Smith,John that I expected to be returned also

The Op wants people with the name smith somewhere in their name field.

doesn't matter if its the the only thing or buried in 3 other names if it contains Smith it should be returned.

However my original post was merely to clarify that there's no need to concatenate the % wild card character it can be used directly.













----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[r937]

i think you missed the point

the value 'smith' is not fed into the query in the LIKE string

it's inside a column!!!

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[vacunita]

I just realized the mistake.


Sorry, yes, of course its a column not a string.

That's what I get for trying to look at SQL without my morning coffee.

OOOOPS!. Sorry.



----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[CLJBville]

Towerbase, Thank you! Your suggestion does exactly what I wanted!