title: Why do multiple-table joins produce duplicate rows?
body: <p>Let's say I have three tables A, B, and C. Each has two columns: a primary key and some other piece of data. They each have the same number of rows. If I <code>JOIN</code> A and B on the primary key, I should end up with the same number of rows as are in either of them (as opposed to A.rows * B.rows).</p>
  
  <p>Now, if I <code>JOIN</code> <code>A JOIN B</code> with <code>C</code>, why do I end up with duplicate rows? I have run into this problem on several occasions and I do not understand it. It seems like it should produce the same result as <code>JOIN</code>ing <code>A</code> and <code>B</code> since it has the same number of rows but, instead, duplicates are produced.</p>
  
  <p>Queries that produce results like this are of the format</p>
  
  <pre><code>SELECT *
  FROM M
      INNER JOIN S
          on M.mIndex = S.mIndex
      INNER JOIN D
          ON M.platformId LIKE '%' + D.version + '%'
      INNER JOIN H
          ON D.Name = H.Name
          AND D.revision = H.revision
  </code></pre>
  
  <p>Here are schemas for the tables. H contains is a historic table containing everything that was ever in D. There are many M rows for each D and one S for each M.</p>
  
  <p>Table M</p>
  
  <pre><code>    [mIndex] [int] NOT NULL PRIMARY KEY,
      [platformId] [nvarchar](256) NULL,
      [ip] [nvarchar](64) NULL,
      [complete] [bit] NOT NULL,
      [date] [datetime] NOT NULL,
      [DeployId] [int] NOT NULL PRIMARY KEY REFERENCES D.DeployId,
      [source] [nvarchar](64) NOT NULL PRIMARY KEY
  </code></pre>
  
  <p>Table S</p>
  
  <pre><code>[order] [int] NOT NULL PRIMARY KEY,
  [name] [nvarchar](64) NOT NULL,
  [parameters] [nvarchar](256) NOT NULL,
  [Finished] [bit] NOT NULL,
  [mIndex] [int] NOT NULL PRIMARY KEY,
  [mDeployId] [int] NOT NULL PRIMARY KEY,
  [Date] [datetime] NULL,
  [status] [nvarchar](10) NULL,
  [output] [nvarchar](max) NULL,
  [config] [nvarchar](64) NOT NULL PRIMARY KEY
  </code></pre>
  
  <p>Table D</p>
  
  <pre><code>[Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [branch] [nvarchar](64) NOT NULL,
  [revision] [int] NOT NULL,
  [version] [nvarchar](64) NOT NULL,
  [path] [nvarchar](256) NOT NULL
  </code></pre>
  
  <p>Table H</p>
  
  <pre><code>[IdDeploy] [int] IDENTITY(1,1) NOT NULL,
  [name] [nvarchar](64) NOT NULL,
  [version] [nvarchar](64) NOT NULL,
  [path] [nvarchar](max) NOT NULL,
  [StartDate] [datetime] NOT NULL,
  [EndDate] [datetime] NULL,
  [Revision] [nvarchar](64) NULL,
  </code></pre>
  
  <p>I didn't post the tables and query initially because I am more interested in understanding this problem for myself and avoiding it in the future.</p>