There is a table

CREATE TABLE lite_db.accounts ( id_account int(11) NOT NULL AUTO_INCREMENT, email varchar(50) NOT NULL, password varchar(255) NOT NULL, login varchar(255) DEFAULT NULL, date_add date NOT NULL, PRIMARY KEY (id_account) ) 

and table

 CREATE TABLE lite_db.accounts_status ( account_id int(11) NOT NULL, service_id int(11) NOT NULL, status tinyint(4) NOT NULL ) 

You need to select some values ​​from the accounts table, but only for those lines (accounts) for which the accounts_status table does not contain entries with a specific service_id . In the second table there may be several entries for one account, which differ only in the service_id parameter.

The first thought is to do it like this:

 SELECT acc.id_account, acc.email, acc.password, acc.login FROM accounts acc LEFT JOIN accounts_status st ON acc.id_account=st.account_id WHERE st.service_id NOT in(1) GROUP BY acc.id_account LIMIT 10 

Such a query works correctly (according to its logic), but does not give the desired result - it simply returns all the rows for which the table accounts_status contains entries with a service_id other than 1.

How to get only those records for which there are no records in the table accounts_status with 1 or another specified value of service_id ?

    1 answer 1

    Try this:

     SELECT acc.id_account, acc.email, acc.password, acc.login FROM accounts acc where acc.id_account NOT IN( select account_id from accounts_status st WHERE st.service_id =1); 
    • Thanks It works! Is it possible to solve the problem without nested SELECT? - andrshpa
    • one
      It is possible to differently realize the difference of two sets. For example, EXCEPT. MySQL does not seem to support this standard statement. - Sergey Moiseenko