CODEWITHSUNDEEP
phpmysqlsqlwordpress
Ionut Necula
Ionut Necula

Reputation: 11472

How to select all meta values of certain meta_key's?

I'm trying to make a query but I can't get the grips of it. I want to select all records by certain meta_keys. It works fine when i query by one meta key. But when I want to make the query by two meta_key's it's returning nothing. Can someone please help me?!

The query that works:

SELECT *
FROM `uszc_users` AS u, `uszc_utilizatori_acord` AS ua, `uszc_usermeta` AS ub
WHERE ua.email = u.user_email
AND ub.user_id = u.id
AND ub.meta_key = 'first_name'
AND acord = 'DA'
LIMIT 0 , 30

And the query that returns nothing:

SELECT *
FROM `uszc_users` AS u, `uszc_utilizatori_acord` AS ua, `uszc_usermeta` AS ub
WHERE ua.email = u.user_email
AND ub.user_id = u.id
AND ub.meta_key = 'first_name'
AND ub.meta_key = 'last_name'
AND acord = 'DA'
LIMIT 0 , 30

I have 3 tables, table uszc_usermeta contains first_name and last_name which are in the meta_value column both.

Upvotes: 1

Views: 1753

Answers (3)

Alex
Alex

Reputation: 17289

You can't have any record with 

AND ub.meta_key = 'first_name'
AND ub.meta_key = 'last_name'

That has no sense, so just that simple:

SELECT *
FROM `uszc_users` AS u, `uszc_utilizatori_acord` AS ua, `uszc_usermeta` AS ub
WHERE ua.email = u.user_email
AND ub.user_id = u.id
AND (ub.meta_key = 'first_name'
OR ub.meta_key = 'last_name')
AND acord = 'DA'
LIMIT 0 , 30

UPDATE Since the OP said that @bhelmet posted correct answer I want to clarify for OP how good query should look like imho:

SELECT u.*, ub.meta_value first_name, uc.meta_value last_name
FROM `uszc_users` AS u  
INNER JOIN `uszc_utilizatori_acord` AS ua
ON ua.email = u.user_email
  AND ua.acord = 'DA'
INNER JOIN  `uszc_usermeta` AS ub
ON ub.user_id = u.id
  AND ub.meta_key = 'first_name'
INNER JOIN `uszc_usermeta` AS uc
ON uc.user_id = u.id
  AND uc.meta_key = 'last_name'
LIMIT 0 , 30

Upvotes: 1

Avag Sargsyan
Avag Sargsyan

Reputation: 2523

To check two conditions for 'usermeta' you should join table twice. You need something like this:

SELECT *
FROM `uszc_users` AS u, `uszc_utilizatori_acord` AS ua, `uszc_usermeta` AS ub, `uszc_usermeta` AS uc
WHERE ua.email = u.user_email
AND ub.user_id = u.id
AND ub.meta_key = 'first_name'
AND uc.user_id = u.id
AND uc.meta_key = 'last_name'
AND acord = 'DA'
LIMIT 0 , 30

Upvotes: 1

Palpatim
Palpatim

Reputation: 9262

You're asking for all records where meta_key equals BOTH 'first_name' and 'last_name'. You probably want to do something like AND ub.meta_key IN ('first_name', 'last_name').

SELECT *
FROM `uszc_users` AS u, `uszc_utilizatori_acord` AS ua, `uszc_usermeta` AS ub
WHERE ua.email = u.user_email
AND ub.user_id = u.id
AND ub.meta_key IN ('first_name', 'last_name')
AND acord = 'DA'
LIMIT 0 , 30

Upvotes: 1

Related Questions