title: Joining two tables, with a primary key to many foreign keys
body: <p>I have two tables in SQL and I want to make a query where I can get all the columns from the first table and the associated colour (based on the id) from the second table. </p>
  
  <p>The result should concatenate the colours from the second table so that each row has a unique primary key (I know you can't have multiple primary key values)</p>
  
  <p><a href="https://i.stack.imgur.com/hIfQR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hIfQR.png" alt="enter image description here"></a></p>
  
  <p>I have tried an inner join but this results in duplicate primary keys. (See below)</p>
  
  <p>I have also tried a union but I receive an error saying there needs to be an equal amount of columns for a union.</p>
  
  <pre><code>SELECT [Table1].[ID], [Table1].[Name], [Table1].[Job], [Table2].[Colour]
  FROM 
  [Table1]
  INNER JOIN
  [Table2] ON [Table1].[Id]  = [Table2].[Id]
  </code></pre>