Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Posts
    9

    Unanswered: Basic Join Question

    Say I have two tables, Table1 and Table2. Table1 has a primary key field "ID", and Table2 has a foreign key of "ID". Table1 has a one-to-many relationship with Table2, ie. there is only one entry for an ID in Table1, but there are many entries in Table2 that have that ID.

    If I want to join Table1 with Table2 where Table1.ID = Table2.ID, what does the result look like?

    I'm using SQL Server2000 if that makes a difference.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the result of the join looks like this

    stooge curly
    stooge larry
    stooge moe
    reindeer dasher
    reindeer dancer
    reindeer prancer
    reindeer vixen
    mercury NULL
    venus NULL
    earth luna
    mars deimos
    mars phobos
    jupiter ... (i'm too tired to list them all, i think there's 61 of them)


    note that the rows with NULL for table2.id are only seen in a left outer join

    i just thought i'd mention that, because there are several different kinds of join

    rudy
    http://r937.com/

  3. #3
    Join Date
    Aug 2003
    Posts
    9
    So if I have tables like the following:

    Table_Planet
    Planet_ID (pk)
    Planet_Name

    Table_Moon
    Planet_ID (fk)
    Moon_Name

    and I join on Planet_ID, then the output will look like:

    001, earth, luna
    002, mars, deimos
    002, mars, phobos
    003, jupiter, moon1
    003, jupiter, moon2
    003, jupiter, moon3 (etc.)

    is that correct?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that's right

    what you show is the result of an inner join -- mercury and venus have no moon, so they "drop out" of the inner join

    rudy

  5. #5
    Join Date
    Aug 2003
    Posts
    9
    Thanks for your help Rudy.

    One more question. If I want to join (inner) three tables instead of two, and the third table is related to the first in the same way as the second table, what kind of output does that generate, and how do you construct that join statement?

    Table_Planet
    Planet_ID (pk)
    Planet_Name

    Table_Moon
    Planet_ID (fk)
    Moon_Name

    Table_Inhabitants
    Planet_ID (fk)
    Inhabitant (for our example, lets pretend there are aliens)
    Last edited by InquisIdiot; 08-20-03 at 10:24.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, then you're in trouble

    you will get a "cross join" effect

    let's say all three stooges live on mars

    mars curly deimos
    mars curly phobos
    mars larry deimos
    mars larry phobos
    mars moe deimos
    mars moe phobos

    basically, if two unrelated tables (planet inhabitants and planet moons) are joined to the same table, you will get this cross-join effect

    not much you can do about it except "don't do that then"
    ( http://www.jargon.net/jargonfile/d/Dontdothatthen.html )

  7. #7
    Join Date
    Aug 2003
    Posts
    9
    I guess I "won't do that then", although it would be nice. If we were somehow able to relate Table_Moon and Table_Inhabitants, would it then be feasible? Join moon and inhabitants and then join the result to planet?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, "join moon and inhabitants" is the part that is going to give you the cross-join effect

  9. #9
    Join Date
    Aug 2003
    Posts
    9
    Even if they were related? So that means that joining more than two tables is not ever advisable?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, no, no

    if they were related, you wouldn't have the cross-join problem

    you can join as many tables together as you like, provided they are related

    you really ought to try it yourself with a few test tables

    you say you are using sql server 2000, so get busy and create some tables and some joins...

  11. #11
    Join Date
    Aug 2003
    Posts
    9
    I'll do that. I appreciate all of your help!

Posting Permissions

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