James V James V - 2 months ago 13
MySQL Question

MySQL, WHERE to contain seperate table

I've managed to get the first part of my query working fine, it's the final part with the date check that is throwing me.

SELECT * FROM users WHERE available LIKE 'Yes' AND users.id NOT IN (SELECT player_id FROM match_request WHERE club_id LIKE '1000000003') AND (clubs.match_date >= CURRENT_DATE() WHERE clubs.id LIKE '1000000003')


It's this part that I can't work out:

(clubs.match_date >= CURRENT_DATE() WHERE clubs.id LIKE '1000000003')


I get the error message


"#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'WHERE clubs.id LIKE '1000000003') LIMIT 0, 25' at line 1"


But I don't know how to fix this.

Answer Source

Replace where with and

SELECT * 
FROM users 
WHERE available LIKE 'Yes' 
AND users.id NOT IN 
(
   SELECT player_id 
   FROM match_request 
   WHERE club_id LIKE '1000000003'
) 
AND clubs.match_date >= CURRENT_DATE() 
AND clubs.id LIKE '1000000003'
^^^-----here

and you need to join the clubs table if you refer to any columns of that table