Mysql – Return “parent” rows where only one “child” record exists in one-to-many relationship

mariadbMySQLrelations

Having a one-to-many relation, I am trying to get records in the accounts table where only one record exists in the accounts_prop table.

I have the following query, but it does not do the desired. The query returns records with more records in the accounts_prop table.

select *
from `accounts` 
where exists (
    select *
    from `accounts_prop` 
    where `accounts`.`account_id` = `accounts_prop`.`account_id` 
      and `von` >= '2017-08-25'
    having count(account_id) = 1
) limit 3

Best Answer

Setup sample data:

create table accounts (account_id int, von datetime);
insert into  accounts (account_id, von) values (1,'2017-08-01'), (2,'2017-09-01'), (3,'2017-10-01');

create table accounts_prop (account_id int, propname varchar(30));
insert into  accounts_prop (account_id, propname) values (1,'pname1'), (2,'pname1'), (2,'pname2'), (3,'pname1');

Sample data joined together:

select a.account_id,
       a.von,
       ap.propname
from   accounts a
join   accounts_prop ap
on     a.account_id = ap.account_id
order by 1,2,3;

account_id | von                 | propname
---------- | ------------------- | --------
         1 | 2017-08-01 00:00:00 | pname1  
         2 | 2017-09-01 00:00:00 | pname1  
         2 | 2017-09-01 00:00:00 | pname2  
         3 | 2017-10-01 00:00:00 | pname1

Proposed query:

select a.*
from   accounts a
where  a.von >= '2017-08-25'
and    (select count(*)
        from   accounts_prop ap
        where  ap.account_id = a.account_id) = 1
order by 1,2;

account_id | von                
---------- | -------------------
         3 | 2017-10-01 00:00:00

Here's a dbfiddle for the above.

Related Question