CODEWITHSUNDEEP
mysqlsql
rdhd
rdhd

Reputation: 95

How to do intersect of 2 queries in MySQL

I have 3 tables 1. users

name      surname  id     birthdate
Lowell    Bishop    1   1981-05-19
Rodney    Miller    2   1968-01-15
Binyamin  Boone     3   1991-12-21

2.friendships

userid1 userid2  timestamp
1       2      2018-12-03 20:11:03
1       3      2018-02-02 05:36:32
2       3      2018-01-16 02:50:08

3.likes

userid  postid   timestamp
1   4   2018-05-15 21:38:52
2   195 2018-11-17 09:28:43
3   252 2018-10-26 07:08:12

I got 2 queries

/Query get users who has more than 5 friends in March 2018/

SELECT  count(u1.id) as friends_count,
u1.id, u1.name,u1.surname FROM users u1
INNER JOIN friendships f ON f.userid1 = u1.id
INNER JOIN users u2 ON u2.id = f.userid2 
where f.timestamp >= '2025-03-01' and f.timestamp <= '2025-03-31' 
group by u1.id
having count(f.userid1) >= 5 
order by u1.id;

/Query get users who has more than 5 likes in March 2018/

SELECT count(u1.id) as likes_count,u1.id, u1.name,u1.surname FROM users u1
INNER JOIN likes l ON u1.id = l.userid
where l.timestamp >= '2025-03-01' and l.timestamp <= '2025-03-31' 
group by u1.id
having count(u1.id) >= 5
order by u1.id;

How to intersect this 2 queries into 1? I use MySQL so intersect gives me an error. I'm trying to get this results into 1 table, smth like this:

friends_count   likes_count   id   name    surname
5               4             78   Joselyn   Lynn

Upvotes: 1

Views: 61

Answers (1)

Fahmi
Fahmi

Reputation: 37473

You can try below - using subquery

select * from
(
SELECT  count(u1.id) as friends_count,
u1.id, u1.name,u1.surname FROM users u1
INNER JOIN friendships f ON f.userid1 = u1.id
INNER JOIN users u2 ON u2.id = f.userid2 
where f.timestamp >= '2025-03-01' and f.timestamp <= '2025-03-31' 
group by u1.id
having count(f.userid1) >= 5 
)A inner join 
(
SELECT count(u1.id) as likes_count,u1.id, u1.name,u1.surname FROM users u1
INNER JOIN likes l ON u1.id = l.userid
where l.timestamp >= '2025-03-01' and l.timestamp <= '2025-03-31' 
group by u1.id
having count(u1.id) >= 5
)B on A.ud=B.id

Upvotes: 1

Related Questions