CODEWITHSUNDEEP
mysqlsqldatabase
Keith
Keith

Reputation: 4204

How do I return a row from one table with the number of matching id's from a second table in MySQL?

I have two tables, one of discussions and one of responses. I'm wondering how to pull back each discussion row along with the number of rows from the responses table with the matching discussion ID.

table discussions

table responses

So I'd love to be able to get each row of discussions along with a COUNT() of all the responses rows that have its discussion_id

This is about as far as I got:

SELECT
    d.id,
    d.creator,
    d.date,
    d.title,
    (select count(select * from r where r.discussion_id = d.id) from r) num
FROM 
    discussions d, responses r

Am I on the right track here?

Thanks for your time!

Upvotes: 3

Views: 82

Answers (5)

luksch
luksch

Reputation: 11712

SELECT d.id, d.creator, d.date, d.title, COUNT(r.id) AS num 
FROM discussions AS d 
  INNER JOIN responses AS r ON r.discussion_id = d.id
GROUP BY d.id

As pointed out by Crwydryn you could also use a LEFT JOIN instead of the INNER JOIN. The difference would be that you also count the discussions with no resonses with the LEFT JOIN - the num column will then be 0.

Upvotes: 2

Crwydryn
Crwydryn

Reputation: 850

I would suggest using a LEFT JOIN. A standard join will return only Discussions that have a related Response entry, so if the response count is 0 (i.e. no responses for that discussion), a query with a standard join won't return that discussion record. Also, this is more performant than a subquery.

select d.id,
    d.creator,
    d.date,
    d.title,
    count(r.id) as num
FROM discussions d 
    LEFT JOIN responses r on d.id = r.discussion_id
GROUP BY d.id

Upvotes: 1

Amit Singh
Amit Singh

Reputation: 8109

Try like this...simply use group by....it will give what u want

 SELECT
        d.id,
        d.creator,
        d.date,
        d.title,
       (Select Count(*) from response where response.id= d.id) as num
    FROM 
        discussions d

Upvotes: 1

Muhannad A.Alhariri
Muhannad A.Alhariri

Reputation: 3912

Please check the following Query 

SELECT
    d.id,
    d.creator,
    d.date,
    d.title,
   count(r.id)  NUM
FROM 
dicussion d join response r on d.id = r.discussion_id  
GROUP BY d.id

Upvotes: 1

chue x
chue x

Reputation: 18803

You are close. Try:

SELECT
    d.id,
    d.creator,
    d.date,
    d.title,
    (select count(*) from r where r.discussion_id = d.id) num
FROM 
    discussions d

Upvotes: 1

Related Questions