0

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
1

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.

0

Edit: Just saw that you were asking for MySQL. This won't work as MySQL has no support for analytical functions.

The following should work on MySQL now (SQLFiddle):

SELECT
    accounts.*   
FROM
    accounts
JOIN accounts_prop 
    ON accounts.account_id = accounts_prop.account_id
    AND von >= '2017-08-25'
GROUP BY accounts.account_id
HAVING count(1) = 1
LIMIT 3

Old Solution:

One way to tackle this would be using a window function to count rows by account_id and filtering on that:

SELECT * FROM (
    SELECT
        accounts.*,
        count(1) OVER (PARTITION BY accounts.account_id) row_count
    FROM
        accounts
    JOIN accounts_prop ON
        accounts.account_id = accounts_prop.account_id AND von >= '2017-08-25'
) sub
WHERE row_count = 1
LIMIT 3
  • From the OP's comment on my answer, they might actually be using MariaDB, if that matters. – Oreo Aug 25 '17 at 11:17
  • Then my first solution might even work. Don't have access to MariaDB test instance unfortunately to verify – Werter Aug 25 '17 at 11:24
  • the query alone works if I do not use where statemant – fefe Aug 25 '17 at 12:20
0

You are trying to COUNT() stuff without grouping your data with a GROUP BY clause.

Try this:

SELECT `accounts`.*
FROM `accounts` 
INNER JOIN (
    SELECT `accounts_prop`.`account_id`
    FROM `accounts_prop` 
    WHERE `von` >= '2017-08-25'
    GROUP BY `accounts_prop`.`account_id`
    HAVING COUNT(account_id) = 1
    ) AS `accounts_prop`
    ON `accounts`.`account_id` = `accounts_prop`.`account_id`
LIMIT 3
  • Thanks for feedback. Adding group by does not solve the problem in my case – fefe Aug 25 '17 at 8:33
  • Hmm, interesting. Try my new edit. – Oreo Aug 25 '17 at 8:37
  • this has some syntax errors – fefe Aug 25 '17 at 9:43
  • I don't actually have MySQL installed on my machine, so I can't test out the syntax errors... Did you try Werter's answer to your question? – Oreo Aug 25 '17 at 9:52
  • on mariadb does not work – fefe Aug 25 '17 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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