Sql - count by other column value not id with join-CodePudding

I have two tag_group and tag tables bound by a foreign key and wrote an sql query with Postgres 14.

tag_group table:

id	slug
6	awesome-team

tag table:

id	team_id	name	tag_group_id
1	3	Tag 1	6
2	3	Tag 2	6

I want to count all tags from tag table that belong to specific tag_group. I managed to do so with:

SELECT count(*) AS total_count FROM tag
JOIN tag_group tg
ON tg.id = tag_group_id   
WHERE tag_group_id = 6 AND tg.team_id = 3;

I got confused of how can I do the same operation but with the exception that instead of having and ID as parameter I have only slug instead.. Can I still retrieve results if I have only slug value from tag_group table ?

CodePudding user response：

yes you can query by slug too you just need to change you query like this

SELECT count(*) AS total_count FROM tag
JOIN tag_group tg
ON tg.id = tag.tag_group_id   
WHERE tag.slug = 'slug' AND tg.team_id = 3;

as you can see i change the join condition like this

ON tg.id = tag.tag_group_id

it means join all record by the id

but in the query where condition i change query like this

WHERE tag.slug = 'slug' AND tg.team_id = 3;

now you can filter your result by slug