CODEWITHSUNDEEP
mysqlsql
qqruza
qqruza

Reputation: 1417

SQL query not getting results with multiple ANDs

I have got a SQL query to get data from my MySQL database:

SELECT wp_users.ID, wp_users.time, wp_users.display_name 
FROM wp_users 
INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id 
WHERE wp_usermeta.meta_key = 'wp_capabilities' 
AND wp_usermeta.meta_value LIKE '%user%' 
AND wp_users.time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MINUTE)) 
ORDER BY wp_users.display_name";

It is getting a list of all users without the second AND statement:

AND wp_users.time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MINUTE))

Is there anything I can do to have two ANDs to make it working?

Many thanks in advance

Upvotes: 0

Views: 65

Answers (1)

stanhope
stanhope

Reputation: 346

I don't see an error with the query. I think the error is an artefact of your data set. From the comments on your question, I understand that the original query:

SELECT wp_users.ID, wp_users.time, wp_users.display_name 
FROM wp_users 
INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id 
WHERE wp_usermeta.meta_key = 'wp_capabilities' 
AND wp_usermeta.meta_value LIKE '%user%' 
AND wp_users.time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MINUTE)) 
ORDER BY wp_users.display_name";

returns no results, but that removing the inner join and join conditions:

SELECT wp_users.ID, wp_users.time, wp_users.display_name 
FROM wp_users 
WHERE wp_users.time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MINUTE)) 
ORDER BY wp_users.display_name";

produces what you were expecting. This implies that:

  1. there are users whose wp_users.time is within the target interval, but
  2. these users don't have corresponding entries in the wp_usermeta table and
  3. there are other users who do have corresponding entries, but they don't match the wp_users.time interval

Inner joins require matching key entries: http://www.w3schools.com/sql/sql_join_inner.asp

I think your choices are:

  1. alter the data set so that 100% of users participate in the wp_usermeta table
  2. convert the inner join to a left join or full outer join and relax the where constraints, e.g.: SELECT wp_users.ID, wp_users.time, wp_users.display_name FROM wp_users LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id ON wp_usermeta.meta_key = 'wp_capabilities' AND wp_usermeta.meta_value LIKE '%user%' WHERE wp_users.time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MINUTE)) ORDER BY wp_users.display_name;

That will technically make it work, but your query will be accepting users who only have NULLs for the corresponding (non-participating) wp_usermeta entries.

Upvotes: 2

Related Questions