title: MySQL Duplicating Row Results in Table When Searching Across Multiple Tables
body: <p>I have three tables in a mySQL db one for users, one for colours and one for the linking of the users to the colours</p>
  
  <pre><code>Table 1 users
  ==============
  ID | Name
  --------------
  1  | John
  2  | Jayne
  3  | Fred
  
  Table 2 colours
  ==============
  ID | Colour
  --------------
  1  | Blue
  2  | Red
  3  | Yellow
  
  Table 3 link
  ==============
  ID | Name | Colour
  --------------
  1  | 1    | 1
  2  | 1    | 2
  3  | 2    | 1
  4  | 3    | 2
  5  | 3    | 3
  </code></pre>
  
  <p>As you can see, some users have more than one favourite colour (yeah, i know, how annoying).</p>
  
  <p>At the moment, I can show them in a table, with their favourite colour(s) in a column.</p>
  
  <p>BUT, I want to be able to filter the table results by colour.</p>
  
  <p>I can do it no problem with having a filter of just one colour, BUT the problem comes along with two colours.</p>
  
  <p>If I want to see which user has selected for example Blue AND Red, I get a result of zero.</p>
  
  <p>How can I get this result, without creating a search which results in each row being dedicated to a colour and then in turn showing the same user twice (one for red one for blue).</p>
  
  <p>I hope this makes sense</p>
  
  <p>THANKS IN ADVANCE</p>
  
  <p>EDIT</p>
  
  <p>An example query I have used is</p>
  
  <pre><code>SELECT * FROM users, colours, link WHERE users.id = link.name AND link.colour = colours.id
  </code></pre>
  
  <p>Alternatively to show for specific colour</p>
  
  <pre><code>SELECT * FROM users, colours, link WHERE users.id = link.name AND link.colour = colours.id AND link.colour = 1
  </code></pre>
  
  <p>But for double filter which shows duplicates</p>
  
  <pre><code>SELECT * FROM users, colours, link WHERE users.id = link.name AND link.colour = colours.id AND link.colour = 1 OR link.colour = 2
  </code></pre>