Mysql – Trying to run query and getting error: Error 1064 (42000)

errorsMySQLsubquery

I am trying to get a list of users from 2 different tables. I am using a sub query and the 2 separate queries run fine.

I am getting this error:

ERROR 1064 (42000): 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 'IN (SELECT
DISTINCT(flwusertest_users.ID),flwusertest_users.display_name,flwuser'
at line 1

Here is the code I am using:

select 
  flwusertest_users.ID, 
  flwusertest_users.display_name, 
  flwusertest_users.user_email 
from flwusertest_users  
INNER JOIN flwusertest_usermeta ON flwusertest_users.ID=flwusertest_usermeta.user_id 
WHERE (flwusertest_usermeta.meta_key='_um_last_login' 
         and flwusertest_usermeta.meta_value NOT BETWEEN '1483228800' and '1520952132') 
AND NOT IN (
  SELECT DISTINCT(flwusertest_users.ID),
    flwusertest_users.display_name,
    flwusertest_users.user_email 
  from flwusertest_users 
  INNER JOIN flwusertest_em_bookings ON flwusertest_users.ID=flwusertest_em_bookings.person_id 
  WHERE flwusertest_em_bookings.booking_status=1);

Any help is much appreciated.

Best Answer

select 
       flwusertest_users.ID, 
       flwusertest_users.display_name, 
       flwusertest_users.user_email 
from 
      flwusertest_users  
INNER JOIN 
      flwusertest_usermeta 
      ON flwusertest_users.ID = flwusertest_usermeta.user_id 
WHERE (
      flwusertest_usermeta.meta_key = '_um_last_login' 
      AND flwusertest_usermeta.meta_value NOT BETWEEN '1483228800' and '1520952132'
      ) 
AND <some_field> NOT IN (SELECT DISTINCT(flwusertest_users.ID)
                         FROM   
                                flwusertest_users 
                         INNER JOIN 
                                flwusertest_em_bookings 
                               ON flwusertest_users.ID=flwusertest_em_bookings.person_id 
                        WHERE 
                              flwusertest_em_bookings.booking_status=1);

There is more than one error in your sentence.

AND <some_field> NOT IN (SELECT DISTINCT(flwusertest_users.ID),
                                flwusertest_users.display_name,
                                flwusertest_users.user_email

You cannot return more than one field to use it with IN. You should return only 

AND <some_field> NOT IN (SELECT DISTINCT(flwusertest_users.ID)
                         FROM   
                                flwusertest_users 
                         INNER JOIN 
                                flwusertest_em_bookings 
                               ON ...
Related Question