CODEWITHSUNDEEP
mysql
vfioox
vfioox

Reputation: 738

SELECT and COUNT from relative table

I have two tables, forums and topics. Each topic belongs to a forum, which is specified by a forum_id. To each forum I want to return the belonging topics count in the same query.

SELECT  (
SELECT *
FROM  forums
) AS forums,
(
SELECT COUNT(*)
FROM  topics t
WHERE t.forum_id=forums.id
) AS topic_count

How can I pass the ID from first select and compare it in the second?

Upvotes: 0

Views: 109

Answers (2)

Mike Meinz
Mike Meinz

Reputation: 506

Select *, (Select Count(*) From topics t2 
    Where t2.forum_id=t1.Forums_id) As [Topic Count] 
    From forums t1
    Order By t1.forums_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Is this what you want?

SELECT f.*, COUNT(*)
FROM forums f
     topics t
     on t.forum_id = forums.id
GROUP BY f.id;

Upvotes: 1

Related Questions