Skip to content

Instantly share code, notes, and snippets.

@tjad
Created October 12, 2011 11:57
Show Gist options
  • Save tjad/1281037 to your computer and use it in GitHub Desktop.
Save tjad/1281037 to your computer and use it in GitHub Desktop.
Most common associations
Have table A, B
Have a joining table C (a_id,b_id)
SELECT DISTINCT A.*
FROM A
INNER JOIN C ON C.a_id=A.id
WHERE C.b_id = 5
The above is a simple query that will find me stuff associated with the specified b_id. I would like it to only return records if the b_id specified is in the 3 most commonly associated
E.G(in this example, if I specified 5, it should only return record with id 2 from Table A as id 5 from table B is the only association and hence in the 3 most commonly associated. It shouldn't return record with id 1 from Table A as its 3 most commonly associated are 1,2,3
Table C
a_id,b_id
1,1
1,2
1,2
2,5
1,1
3,4
1,3
1,2
1,3
1,5
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment