Using INNER JOIN multiplies selected rows?

Question

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 */

Answer 1

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'