First 3-way join

Databases
1

I have three tables:

Table: nick_test
name | nickname
JAMES | JAMES
JIM | JIM
JAMES | JIM
JIM | JAMES
JOHN | JOHN

Table: master_test
ln | fn | complete_addr
VANDERSLICE | JIM | 4420 SERRA
VANDERSLICE | JOHN | 123 MAIN

Table: name_src
ln | fn
VANDERSLICE | JAMES
JONES | JASON

I’m selecting records I don’t want. I expected only the JIM record from master_test, but the JIM and JOHN records are selected from master_test with this code:

SELECT master_test.complete_address, master_test.ln, master_test.fn
                                   FROM master_test
                                   LEFT JOIN name_src
                                                ON master_test.ln = name_src.ln
                                   INNER JOIN nick_test
                                                ON master_test.fn = nick_test.name
                                                AND master_test.fn = nick_test.nickname

What am I doing wrong?

2

remove the LEFT JOIN to name_src, and try again

:slight_smile:

3

I tried RIGHT JOIN and INNER JOIN. Both keywords produced two rows. I’m looking for one row returned. Is it just that keyword that’s wrong? If so, what other kinds of joins should I consider?

4

before thinking about what kind of joins to name_src you want, please explain why you want to join to name_src at all… because your query isn’t using it in any way

also, have a look at the join conditions for the join to nick_test – you seem to want only those results where master_test.fn is equal to ~both~ nick_test.name and nick_test.nickname, which might be perfectly valid but…

5

I see what you mean. I think a JOIN with a WHERE is what I need.

This returns the three rows I expected:


SELECT master_test.complete_address, master_test.ln, master_test.fn 
          FROM master_test 
          JOIN name_src
		ON master_test.ln = name_src.ln

I think a WHERE is needed to target JIM in master_test

Something like this:


SELECT master_test.complete_address, master_test.ln, master_test.fn 
         FROM master_test 
   	 JOIN name_src
	     ON master_test.ln = name_src.ln
	 WHERE 
	     master_test.fn = nick_test.name 
            AND master_test.fn = nick_test.nickname

Obviously the WHERE is wrong. If I’m on the right track, how to I make proper reference to nick_test?

6

instead of struggling with joins, let’s take a step backwards

what is the purpose of each of the three tables?

7

I need to match the fn and ln in NAME_SRC to the fn and ln in MASTER_TEST so I can target the complete_addr in MASTER_TEST and its associated fn and ln. The nick_test table exists to target related firstnames (jim/james, james/jim, etc).

8

try this –

SELECT master_test.ln
     , master_test.fn
     , master_test.complete_addr
  FROM name_src
INNER
  JOIN nick_test
    ON nick_test.name = name_src.fn
INNER
  JOIN master_test
    ON master_test.ln = name_src.ln
   AND master_test_fn = nick_test.nickname
9

Bullseye! I can see how that would knock-out JOHN. I never thought of approaching it from the inside out. Excellent topic r937. Thank-you very much for your help.