CODEWITHSUNDEEP
sqlsqlite
bloodless2010
bloodless2010

Reputation: 349

Adding count from another table to query with inner join

I have the following query:

SELECT 
    * 
FROM `SAS_applications` 
INNER JOIN `SAS_forms` on SAS_applications.form_id = SAS_forms.fid 
ORDER BY SAS_applications.id DESC 
LIMIT 10;

and the following SQLite setup: enter image description here

This query works fine, but after adding another table to include replies/comments, I ran into some difficulty counting how many comments where a part of each 'application'.

enter image description here

I'm trying to find a way I can count(*) the amount of entries inside of SAS_comments with the same form_id of each return in the original query, I can achieve this individually by doing

SELECT count(*) FROM `SAS_comments` WHERE form_id = 1

Does anyone know how I can achieve this? I've tried various joins and nests but my SQL syntax knowledge isn't the best.

Upvotes: 0

Views: 31

Answers (2)

CL.
CL.

Reputation: 180060

You can just use a correlated subquery:

SELECT *,
       (SELECT count(*) FROM SAS_comments WHERE form_id = SAS_forms.fid)
FROM ...

Upvotes: 1

ppenchev
ppenchev

Reputation: 157

You need to use one more join with SAS_comments, Count and Group By and your code should looks like close to this 

SELECT 
    count(`SAS_comments`.form_id), `SAS_comments`.id
FROM `SAS_applications` 
INNER JOIN `SAS_forms` on SAS_applications.form_id = SAS_forms.fid 
JOIN `SAS_comments` on `SAS_forms`.form_id = `SAS_comments`.form_id
GROUP BY `SAS_comments`.id
ORDER BY SAS_applications.id DESC

Upvotes: 0

Related Questions