Sql Count on Many

Question

I have three tables

message

id | statement | date

the functions

id | feature

post_feature (much for the table between Post and Feature)

post_id | feature_id

I want to run a query that will give me the number of different functions and corresponding functions for messages that are in a given period. I just started learning SQL, and I can't hack this one.

I tried the following but did not get the 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 

Answer 1

You can try the following:

  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 

Answer 2

 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 

Answer 3

Your SQL is pretty creative. However, your joining the IN clause contains invalid columns. This should be on postid for postid.

Although this fixes the request, here is the best 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 the information you want to know.

Answer 4

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