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
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
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.