Is there a way to include double condition in HAVING?

Question

I'm currently working on a query that looks like this. There are two tables - members and member_gathering.

SELECT id, city_id, name FROM members
WHERE "id" = "member_id" IN
(
   SELECT "member_id" from member_gathering
   GROUP BY "member_id"
   HAVING COUNT(DATEDIFF("visited","joined">=365))>=5
   ORDER BY "member_id"
)

ORDER BY id*1;

The goal is to have an output of all IDs satisfying the condition of being in more than 5 groups, in which a member is active for more than a year. Being active means having a difference between "visited" and "joined" columns (both are TIMESTAMP) for more than a year (I set that as 365 days).

However, after running, this code shows all the rows in a members table (though manual check of both tables shows that some rows do not satisfy both conditions at the same time).

Any ideas on how to improve the code above? I'm not sure if I can use 'nested' condition inside COUNT(), but all other variants used before show either NULL values or returned all rows in the table, which is obviously not right. Also, I was thinking that problem might be with DATEDIFF function.

All suggestions are welcome: I'm a newbie to MySQL, so I'm not that familiar with it.

UPD: data sample: 1) members

id     city_id    name
2      980        Joey
5      980        Carl
10     1009       Louis
130    1092       Andrea

2) member_gathering

member_id      gathering_id     joined            visited    
2              1             2010-01-01 00:00:00  2010-02-01 00:00:00
2              2             2010-01-01 00:00:00  2010-02-01 00:00:00
5              2             2010-01-01 00:00:00  2010-02-01 00:00:00
10             3             2010-01-01 00:00:00  2010-02-01 00:00:00
130            1             2010-02-01 00:00:00  2013-02-01 00:00:00
130            2             2010-02-01 00:00:00  2013-02-01 00:00:00
130            3             2010-02-01 00:00:00  2014-02-01 00:00:00
130            4             2010-02-01 00:00:00  2018-02-01 00:00:00
130            5             2010-02-01 00:00:00  2015-02-01 00:00:00

Expected result would be only ID 130, thus: 130, 1092, Andreana.

ScaisEdge · Answer 1 · 2019-11-12T15:25:13.563

1

You could use this way

SELECT id, city_id, name FROM members
WHERE member_id   IN
(
 SELECT member_id from member_gathering
 GROUP BY member_id
 HAVING SUM(DATEDIFF(visited, joined) >= 365)>=5
 ORDER BY member_id
)

You should use separated expression for count differente category of datediff and remmeber that count work for not null values so if you want obtain the totale for true values you should sue SUM

edited Nov 12 '19 at 15:25

answered Nov 12 '19 at 12:10

ScaisEdge

131,976
10
91
107

Thanks, it still shows the total number of rows though, any other ideas on by what it might be caused also? – chirstkew109 Nov 12 '19 at 15:22
Update your question add a proper data sample and the expected result ... anyway answer updated .. using member_id – ScaisEdge Nov 12 '19 at 15:24

Salman A · Accepted Answer · 2019-11-12T15:29:07.587

1

I believe you first need to find all records where datediff is 365 days or more. Then find members who have 5 or more such instances. This needs both WHERE and HAVING clause:

SELECT id, city_id, name
FROM members
WHERE id IN (
    SELECT member_id
    FROM member_gathering
    WHERE DATEDIFF(visited, joined) >= 365
    GROUP BY member_id
    HAVING COUNT(*) >= 5
)

edited Nov 12 '19 at 15:29

answered Nov 12 '19 at 12:21

Salman A

262,204
82
430
521

Thanks a lot, but it still shows the total number of rows. Any other ideas by what it can be caused? – chirstkew109 Nov 12 '19 at 15:23
Off topic but what does `SELECT @@SQL_MODE` give you? – Salman A Nov 12 '19 at 15:28
gives the following: 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' – chirstkew109 Nov 12 '19 at 15:50
Checked the last edited variant! Worked smoothly. Thanks for helping! – chirstkew109 Nov 12 '19 at 16:16
There is no `ANSI_QUOTES` in the SQL mode so the `"` were actually treated as strings producing nonsense results. See https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql?rq=1 – Salman A Nov 12 '19 at 19:10

Is there a way to include double condition in HAVING?

2 Answers2