CODEWITHSUNDEEP
sqlsqlite
Roman
Roman

Reputation: 479

How to combine two SQL queries in one

I have tables user, participant and chat. I need to get all users in a specific chat and amount of chats that user in by chat name. For example current tables:

  user      chat          participant
id|name   id|name       user_id|chat_id
1|Mike    1|School            1|1
2|John    2|Football          2|1
3|Sara    3|Gym               1|2
                              3|3

And by keyword "School" I want to get this

Mike|2
John|1

I have two queries to get first and second column in result but don't know how to combine it:

SELECT user.name FROM user 
JOIN participant ON (user.id = participant.user_id) 
JOIN chat ON (participant.chat_id = chat.id) WHERE chat.name = 'School';

That gives me

Mike
John

And

SELECT user.name, COUNT(*) FROM user 
JOIN participant ON (user.id = participant.user_id) GROUP BY user.name;

returns

John|1
Mike|2
Sara|1

So how to combine it?

Upvotes: 0

Views: 579

Answers (2)

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY THIS

SELECT p1.name, COUNT(p.user_id) totUser
FROM participant p 
INNER JOIN (select u.id, u.name FROM participant p 
    inner JOIN chat c ON c.id = p.[chat_id]
    INNER JOIN user u ON u.id = p.user_id
    AND c.name = 'School') p1 ON p1.id = p.user_id
GROUP BY p1.name

Upvotes: 1

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

using Subquery and joins :

            select u.name,count(p.chat_id) as 'Count' from user u
            inner join participant p on p.user_id = u.id
            where 
            p.user_id  in ( select user_id from participant pp inner join chat cc on cc.id = pp.chat_id  where 
            cc.name = 'School' )    
            group by u.name
            order by Count desc

Output :

enter image description here

Upvotes: 1

Related Questions