Jump to content

join with a where


niche

Recommended Posts

I have three tables:Table: nick_testname | nicknameJAMES | JAMESJIM | JIMJAMES | JIMJIM | JAMESJOHN | JOHNTable: master_testln | fn | complete_addrVANDERSLICE | JIM | 4420 SERRAVANDERSLICE | JOHN | 123 MAINVANDERSLICE | JOHN | 789 SOUTHSMITH       |  TIM  | 567 ATable: name_srcln | fnVANDERSLICE | JAMESJONES | JASONThis code returns the the three rows I expected (first three rows in master_test).

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_testSomething 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?

Edited by niche
Link to comment
Share on other sites

That's what I was originally attempting, but no joy. So I decided to I try WHEREHere's my original code:

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

This gave me all of the VANDERSLICE  rows from master_test, but I need to target the JIM VANDERSLICE row from master_test since to join JAMES VANDERSLICE from name_src to JIM VANDERSLICE in master_test requires the nick_test table to join JAMES with JIM.How do I suppress the JOHN rows in master_test and only target the JAMES/JIM row in master_test in a JOIN?

Edited by niche
Link to comment
Share on other sites

I think I'm following you, but I'm still getting the JIM and JOHN rows from master_test when I should only get the JIM row.current code:SELECT master_test.complete_address, master_test.ln, master_test.fn        FROM master_test         JOIN name_src                ON master_test.ln = name_src.ln        JOIN nick_test                WHERE master_test.fn = nick_test.nameAND master_test.fn = nick_test.nickname

Edited by niche
Link to comment
Share on other sites

Yes, but there's no JOHN in the name_src file.  How do I avoid selecting JOHN in the context of this JOIN?

Edited by niche
Link to comment
Share on other sites

I ended-up using an inside out approach:

SELECT master_test.complete_address, master_test.ln, master_test.fn      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

I was becoming certain that I needed a WHERE, but not true as it turned-out.Thanks as always JSG.  I can always count on you.

Edited by niche
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...