CODEWITHSUNDEEP
mysqlsql
rishi
rishi

Reputation: 2554

Sql Count on many to many

I have three tables

post

id | statement | date

features

id | feature

post_feature (many to many table between Post and Feature)

post_id | feature_id

I want to fire a query that will give me count of different distinct features and its respective features for the posts that are in given date period. I have just started learning SQL and I am not able to crack this one.

I tried the following one but not getting correct results.

 SELECT f.feature, count(f.feature)
   FROM  post_feature l 
   JOIN  features f ON (l.featureid = f.id AND l.featureid IN (
   select post.id from post where post.date > 'some_date'))
   GROUP BY f.feature

Upvotes: 6

Views: 9531

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Your SQL is quite creative. However, your join in the IN clause is on the wrong columns. It should be on postid to postid.

Although that fixes the query, here is a better way to write it:

 SELECT f.feature, count(f.feature)
 FROM  post p join
       post_feature pf
       on p.id = pf.postid join
       feature f
       on pf.featureid = f.id
 where post.date > 'some_date'
 GROUP BY f.feature

This joins all the tables, and then summarizes by the information you want to know.

Upvotes: 2

Geordee Naliyath
Geordee Naliyath

Reputation: 1859

select f.feature, count(*)
from  post_feature l inner join features f on l.feature_id = f.id
                     inner join post p on l.post_id = p.id
where p.date > 'some_date'
group by f.feature

Upvotes: 2

podiluska
podiluska

Reputation: 51494

Try

   SELECT f.feature, count(DISTINCT f.feature) 
   FROM  post_feature l  
   JOIN  features f ON (l.featureid = f.id AND l.featureid IN ( 
   select post.id from post where post.date > 'some_date')) 
   GROUP BY f.feature

Upvotes: 0

Akash KC
Akash KC

Reputation: 16310

You can try like this: 

       SELECT f.feature, count(f.feature)
       FROM  post_feature l 
       JOIN  features f ON l.featureid = f.id 
       JOIN  post p ON l.post_id =p.id 
       WHERE p.date > 'some_date'
       GROUP BY f.feature

Upvotes: 12

Related Questions