SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    446
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    first 3-way join

    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:

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    remove the LEFT JOIN to name_src, and try again

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    446
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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...
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    446
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I see what you mean. I think a JOIN with a WHERE is what I need.

    This returns the three rows I expected:
    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
    I think a WHERE is needed to target JIM in master_test

    Something like this:
    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
    	 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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    instead of struggling with joins, let's take a step backwards

    what is the purpose of each of the three tables?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    446
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    try this --
    Code:
    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
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist
    Join Date
    Jun 2010
    Posts
    446
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •