CODEWITHSUNDEEP
mysqlsql
nick
nick

Reputation: 3711

MYSQL Count where active across tables

I have three tables: 

person
-----------
person_id, active

person_team
-----------
person_id, team_id, active

team
-----------
team_id, active

I'd like to get the count on teams from each person where active is true in each table.

So far I have:

SELECT t.id, t.title, t.created_timestamp, COUNT(p_t.tag_id) AS count
FROM team t
LEFT JOIN 
    person_team p_t ON p_t.team_id = t.id AND p_t.active = 1
WHERE 
    t.active = 1
GROUP BY t.id
ORDER BY t.title

This gets the count where team and person - team are active, but doesn't take into account whether person is active. Should I use a sub query or another type of join?

Upvotes: 0

Views: 34

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

You should use a inner join on sub select for get the columns not in group by 

select k.id, t.title, t.created_timestamp, k.count from 
 (  SELECT t.id  COUNT(p_t.tag_id) AS count
FROM team t
LEFT JOIN 
    person_team p_t ON p_t.team_id = t.id AND p_t.active = 1
WHERE 
    t.active = 1
GROUP BY t.id ) k
inner join team t on t.id = k.id

Upvotes: 1

trincot
trincot

Reputation: 350766

You need to add the person table in a join, and count a column from that table:

SELECT t.id, t.title, t.created_timestamp, COUNT(p.id) AS count
FROM team t
LEFT JOIN 
    person_team p_t ON p_t.team_id = t.id AND p_t.active = 1
LEFT JOIN 
    person p ON p_t.person_id = p.id AND p.active = 1
WHERE 
    t.active = 1
GROUP BY t.id
ORDER BY t.title

Upvotes: 1

Related Questions