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

Question

I have tables with this structure.

table #1: users (ID, user_name)

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'

the question is how do I write the query to achieve the above-mentioned result. — mushroom
– mushroom, Commented Nov 2, 2012 at 19:19

Joe Meyer · Accepted Answer · 2012-11-02 19:44:42Z

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

answered Nov 2, 2012 at 19:44

Joe Meyer

4,4452 gold badges23 silver badges28 bronze badges

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

Comments

knittl · Accepted Answer · 2012-11-02 19:56:31Z

1

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%'

edited Nov 2, 2012 at 19:56

answered Nov 2, 2012 at 19:06

knittl

269k59 gold badges339 silver badges405 bronze badges

4 Comments

mushroom Over a year ago

Sorry, one more thing to clarify, the user_meta table may contain other random metas like the user's email. I only want to search from the first_name and last_name concatenated.

knittl Over a year ago

@jon: okay. But that's as easy as adding a WHERE clause, there's nothing complex about it …

mushroom Over a year ago

Thanks for your response, but I don't think you fully understand what I'm looking for. Apologies for my bad explanation. I actually want to get the IDs of users matching the search query. What your query gives only gives me the match for each part.

knittl Over a year ago

@jon: Oh, I thought you want 1 (as in TRUE) when there is a match. I'll add another query (but it is not complex either, it's just a simple JOIN)

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related