title: Can't figure out complex query
body: <p>Could anybody point me in the direction of how to write a complex mysql query? I am familiar with joins, views, and intermediate querying, but cannot quite conceptualise how to go about writing a query to pull out the data I am looking for.</p>
  
  <p>I have three tables, user, user_category and category. user_category is just a bridge table.</p>
  
  <pre><code>user
  
  user_id   name         
  -----------------------
  1         Bob
  2         Tim
  3         Dave
  4         Simon
  5         Ben
  
  user_category
  
  user_id   category_id
  -----------------------
  1         1
  1         5
  1         3
  2         4
  2         1
  3         2
  3         4
  4         4
  4         1
  5         3
  5         5
  
  category
  
  category_id  category   
  -----------------------
  1            drawing    
  2            climbing     
  3            hunting    
  4            fishing    
  5            sleeping   
  </code></pre>
  
  <p>So I can easily pull out a list of users in each category, no problem.</p>
  
  <p>What I want to do, for each of these categories, is show a list of other categories that the list of users in this category is also in. </p>
  
  <p>So if I take fishing as an example, my query would pull out Tim, Dave and Simon as being in that category. I want to list all the other categories that Tim Dave and Simon are in, with a count of how many users are in each of those categories. Like this:</p>
  
  <pre><code>Drawing (2) - this has Tim and Simon in it
  Climbing (1) - this has Dave in it
  </code></pre>
  
  <p>I realise I need to get a list of users in a given category. Simple.</p>
  
  <p>I also need a list of all the categories that each of these users is in (excluding current category).</p>
  
  <p>Then for each category, I need a count of each of the users in each.</p>
  
  <p>I think I could write this with separate nested queries, but I would like to write this in a single query using all the necessary joins if possible, but if anyone can point me towards some reading material or video content that would help me work out how to plan these queries, that would be even better.</p>