2 double joins in the same query

[nichemtktg]

How do I do the same joins for mar_lic.spouse2_ln and mar_lic.spouse2_fn that I’m already doing for
mar_lic.spouse1_ln and mar_lic.spouse1_fn in the same query based on this SELECT:

SELECT
mar_lic.spouse1_ln,
mar_lic.spouse1_fn,
mar_lic.spouse2_ln,
mar_lic.spouse2_fn,
master_test2.ln,
master_test2.fn,
master_test2.address
FROM
mar_lic
JOIN
nick_test
ON
nick_test.name = mar_lic.spouse1_fn
JOIN
master_test2
ON
nick_test.nickname = master_test2.fn
AND
mar_lic.spouse1_ln = master_test2.ln

[r937]

could you take a second to explain master_test2 and nick_test

i understand mar_lic probably means marriage licence, with lastname/firstname columns for two people

but i can’t understand the joins you want to to because i don’t understand the other two tables

you’re joining spouse 1’s firstname to one table and lastname to the other one?

[nichemtktg]

the marriage license cols = mar_lic.spouse1_ln, mar_lic.spouse1_fn, mar_lic.spouse2_ln, mar_lic.spouse2_fn,

the nick_test cols = name, nickname

the master_test2 cols = addr, fn, ln

The purpose of the nick_test table is to allow a firstname match when a nickname is possible (ie jim = james).

so when I target this row in the marriage license table
SMITH JIM JONES MARY

and I have the these rows in the nickname table:
JIM JAMES
JAMES JIM
JIM JIM
JAMES JAMES
MARY MARY

and I need to target these rows in the master_test2 table:

123 MAIN ST SMITH JIM
789 A ST JONES MARY

I need the output to be: 123 MAIN ST SMITH JIM 789 A ST JONES MARY

[r937]

okay, i get it now… the part i didn’t see was that the nick table had entries for JAMES=JAMES and JIM=JIM

here ya go…

SELECT mar_lic.spouse1_ln
     , mar_lic.spouse1_fn
     , mar_lic.spouse2_ln
     , mar_lic.spouse2_fn
     , master1.ln      AS ln1
     , master1.fn      AS fn1
     , master1.address AS address1
     , master2.ln      AS ln2
     , master2.fn      AS fn2
     , master2.address AS address2
  FROM mar_lic
INNER
  JOIN nick_test AS nick1
    ON nick1.name = mar_lic.spouse1_fn  
INNER
  JOIN master_test2 AS master1
    ON master1.fn = nick1.nickname
   AND master1.ln = mar_lic.spouse1_ln  
INNER
  JOIN nick_test AS nick2
    ON nick2.name = mar_lic.spouse2_fn  
INNER
  JOIN master_test2 AS master2
    ON master2.fn = nick2.nickname
   AND master2.ln = mar_lic.spouse2_ln  

[nichemtktg]

The aliases were the missing piece!

Thank-you very much. I greatly value your expertise and willingness to help everyone with their sql questions. Knowing you’re available gives me piece-of-mind.

PS

Without JIM = JIM, I couldn’t make that match given the way the query is set-up.