niche Posted March 5, 2013 Share Posted March 5, 2013 (edited) 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 March 5, 2013 by niche Link to comment Share on other sites More sharing options...
justsomeguy Posted March 5, 2013 Share Posted March 5, 2013 You need to join on the nick_test table also. Link to comment Share on other sites More sharing options...
niche Posted March 5, 2013 Author Share Posted March 5, 2013 (edited) 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 March 5, 2013 by niche Link to comment Share on other sites More sharing options...
justsomeguy Posted March 5, 2013 Share Posted March 5, 2013 You can still have the conditions in the WHERE clause, but you need to join the table to use those fields in the WHERE clause. Link to comment Share on other sites More sharing options...
niche Posted March 5, 2013 Author Share Posted March 5, 2013 (edited) 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 March 6, 2013 by niche Link to comment Share on other sites More sharing options...
justsomeguy Posted March 6, 2013 Share Posted March 6, 2013 It looks like your data matches the condition, the nick_test table has an entry for John. Link to comment Share on other sites More sharing options...
niche Posted March 6, 2013 Author Share Posted March 6, 2013 (edited) Yes, but there's no JOHN in the name_src file. How do I avoid selecting JOHN in the context of this JOIN? Edited March 6, 2013 by niche Link to comment Share on other sites More sharing options...
justsomeguy Posted March 6, 2013 Share Posted March 6, 2013 You're only specifying that the last name match for that join, so move that join to the end and test both first name and last name. 1 Link to comment Share on other sites More sharing options...
niche Posted March 6, 2013 Author Share Posted March 6, 2013 (edited) 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 March 6, 2013 by niche Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now