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 ?