0

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.

1

1 Answer 1

1
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 ...
2
  • I get this error now.... ERROR 1241 (21000): Operand should contain 1 column(s). I believe this is because I want the result to have id, displayname and email listed. When I remove displayname and email. it runs, however, how can I get the three items to display?
    – MrMatt
    Mar 13, 2018 at 16:23
  • DISTINCT is not a function. You are getting the 'distinct' of all 3 columns. Change DISTINCT to, say, MAX.
    – Rick James
    Mar 15, 2018 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.