complex query in mySQL (searching for a user in multiple tables)

4 Views

I have tables with this structure.

table #1: users (ID, user_name)

Read More
1, 'john777'
2, 'andy'
3, 'tom'

table #2: user_meta (meta_ID, user_ID, ‘meta_name’, ‘meta_value’) 

1, 1, 'first_name', 'John'
2, 1, 'last_name', 'Smith'
3, 2, 'first_name', 'Andy'
4, 2, 'last_name', 'Pete'
5, 3, 'first_name', 'Thomas'
6, 3, 'last_name', 'Tan'
7, 3, 'other_random_meta', 'abcxyz'

For you guys familiar with WordPress, this is their database structure.
What I want to do is to get the user IDs of users matching my search term based on the user_name and full name, made up of first_name and last_name.

In other words, I want a search to return 1 for these search terms:
‘joh’, ‘john7’, ‘smith’, ‘john smi’, ‘smith jo’

Post Views: 4

Related posts

Leave a Reply

2 comments 

  1. SELECT DISTINCT ID FROM 
(SELECT ID AS ID FROM users WHERE user_name LIKE 'YOURSEARCH%' 
UNION 
SELECT t1.user_id FROM 
  (SELECT user_id, meta_value AS firstName FROM user_meta WHERE meta_name ='first_name') AS t1 
INNER JOIN 
  (SELECT user_id, meta_value AS lastName FROM user_meta WHERE meta_name ='last_name') AS t2 ON t1.user_id=t2.userID 
WHERE concat_ws(' ',lastName,firstName) LIKE'YOURSEARCH%')

    This should get you the Unique user ID’s that match your search criteria

  2. SELECT * FROM
(
  SELECT user_name AS user FROM users
  UNION ALL
  SELECT meta_value AS user FROM user_meta
  WHERE meta_name = 'first_name' OR meta_name = 'last_name'
)
WHERE user LIKE '%your search%'

    will return all users matching your criteria. Duplicates are not eliminated and returned as is.

    To get a single row indicating the number of matches use SELECT COUNT(*) … and to only get 0 or 1 use SELECT COUNT(*) > 0 …

    Okay, new query. If you want the IDs of matching users, you have to JOIN the two tables together:

    SELECT DISTINCT u.user_id
FROM users u
JOIN user_meta m
ON u.user_id = m.user_id
WHERE u.user_name LIKE '%name%' OR m.first_name LIKE '%name%' OR m.last_name LIKE '%name%'