Using INNER JOIN multiplies selected rows?

inner-join, sql

Solution

It result in Cartesian product because both joins return multiples records. In order to keep results after first `JOIN`, you must ensure others join are selected by unique keys. If tables don't have unique keys for your select, you can make sub-query (INLINE VIEW), using `DISTINCT` or `GROUP BY` to make it work how you want.

Sample:

SELECT viewlearnersonrun.intRunID,
       intlearnerID,
       strFirstname,
       strunitcode,
       strGrade
  FROM tblawardedlearners 
 INNER JOIN viewlearnersonrun
         ON viewlearnersonrun.intID = tblawardedlearners.intLearnerID
 INNER JOIN viewrun
         ON viewrun.intID = viewlearnersonrun.intRunID 
 INNER JOIN (SELECT DISTINCT strUnitCode --, others columns...
               FROM viewunitsonrun
            ) v
         ON  v.strUnitCode = tblawardedlearners.strUnitCode
 WHERE viewlearnersonrun.intRunID = '200GE2'

Problem

There are alot of posts on inner joins but not sure if it quite answers my problem? I have four tables I wish to join in which the first three seem correct, its when I join the 4th table the join multiplies rows (table AB * table C). The 4th table only has 37 rows however each row would need to be specifically inserted several times according to the viewunitsonrun.strUnitCode which are repeated several times in the 1st table (tblawardedlearers). ``` SELECT viewlearnersonrun.intRunID, intlearnerID, strFirstname, strunitcode, strGrade FROM tblawardedlearners INNER JOIN viewlearnersonrun ON viewlearnersonrun.intID = tblawardedlearners.intLearnerID INNER JOIN viewrun ON viewrun.intID = viewlearnersonrun.intRunID /*CORRECT TO THIS POINT */ INNER JOIN viewunitsonrun ON viewunitsonrun.strUnitCode = tblawardedlearners.strUnitCode WHERE viewlearnersonrun.intRunID = '200GE2' /* display only one Course */ ```

Original source