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.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/|||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?|||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|||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)|||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 )|||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?|||no, "join moon and inhabitants" is the part that is going to give you the cross-join effect|||Even if they were related? So that means that joining more than two tables is not ever advisable?|||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...|||I'll do that. I appreciate all of your help!

Bad record in sysforeignkeys

Somehow there is a foreign key that references a table that doesn't exist.
How can I fix this? All I want to do is delete the record, but I get the
error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned the
"Allow modifications to be made directly to the system catlogs" option on,
but now it seems I am running into some hidden check.
What can I do to resolve this?
Robert
"rgrandidier" <rgrandidier@.discussions.microsoft.com> wrote in message
news:5BA969B4-D5B5-4CB4-82D2-B1A48CC575A2@.microsoft.com...
> Somehow there is a foreign key that references a table that doesn't exist.
> How can I fix this? All I want to do is delete the record, but I get the
> error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned
> the
> "Allow modifications to be made directly to the system catlogs" option on,
> but now it seems I am running into some hidden check.
> What can I do to resolve this?
> --
> Robert
What error message are you getting?
I had problems with a PK on a table with computed columns. It was causing
issues with my sysfilegroups table and the db maint plan failing on the
index rebuilds. It failed with an error message about sysfilegroups
indexing being bad. Of course you can't rebuild those indexes, so I tracked
down the sp_fixindex sproc.
I don't know if this will help you or not as it was a PK index issue, but it
might give you some direction.
In order to fix it I had to do the following...
SET ARITHABORT ON
SET ANSI_NULLS ON
exec sp_dboption '<dbname>', 'single user', TRUE
exec sp_fixindex '<indexname>', sysfilegroups, 1
exec sp_dboption '<dbname>', 'single user', FALSE
You may want to try it on your sysforeignkeys table..
Rick Sawtell
MCT, MCSD, MCDBA

Bad record in sysforeignkeys

Somehow there is a foreign key that references a table that doesn't exist.
How can I fix this? All I want to do is delete the record, but I get the
error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned the
"Allow modifications to be made directly to the system catlogs" option on,
but now it seems I am running into some hidden check.
What can I do to resolve this?
--
Robert"rgrandidier" <rgrandidier@.discussions.microsoft.com> wrote in message
news:5BA969B4-D5B5-4CB4-82D2-B1A48CC575A2@.microsoft.com...
> Somehow there is a foreign key that references a table that doesn't exist.
> How can I fix this? All I want to do is delete the record, but I get the
> error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned
> the
> "Allow modifications to be made directly to the system catlogs" option on,
> but now it seems I am running into some hidden check.
> What can I do to resolve this?
> --
> Robert
What error message are you getting?
I had problems with a PK on a table with computed columns. It was causing
issues with my sysfilegroups table and the db maint plan failing on the
index rebuilds. It failed with an error message about sysfilegroups
indexing being bad. Of course you can't rebuild those indexes, so I tracked
down the sp_fixindex sproc.
I don't know if this will help you or not as it was a PK index issue, but it
might give you some direction.
In order to fix it I had to do the following...
SET ARITHABORT ON
SET ANSI_NULLS ON
exec sp_dboption '<dbname>', 'single user', TRUE
exec sp_fixindex '<indexname>', sysfilegroups, 1
exec sp_dboption '<dbname>', 'single user', FALSE
You may want to try it on your sysforeignkeys table..
Rick Sawtell
MCT, MCSD, MCDBA

Bad record in sysforeignkeys

Somehow there is a foreign key that references a table that doesn't exist.
How can I fix this? All I want to do is delete the record, but I get the
error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned th
e
"Allow modifications to be made directly to the system catlogs" option on,
but now it seems I am running into some hidden check.
What can I do to resolve this?
--
Robert"rgrandidier" <rgrandidier@.discussions.microsoft.com> wrote in message
news:5BA969B4-D5B5-4CB4-82D2-B1A48CC575A2@.microsoft.com...
> Somehow there is a foreign key that references a table that doesn't exist.
> How can I fix this? All I want to do is delete the record, but I get the
> error "Object 'dbo.sysforeignkeys' cannot be modified." I already turned
> the
> "Allow modifications to be made directly to the system catlogs" option on,
> but now it seems I am running into some hidden check.
> What can I do to resolve this?
> --
> Robert
What error message are you getting?
I had problems with a PK on a table with computed columns. It was causing
issues with my sysfilegroups table and the db maint plan failing on the
index rebuilds. It failed with an error message about sysfilegroups
indexing being bad. Of course you can't rebuild those indexes, so I tracked
down the sp_fixindex sproc.
I don't know if this will help you or not as it was a PK index issue, but it
might give you some direction.
In order to fix it I had to do the following...
SET ARITHABORT ON
SET ANSI_NULLS ON
exec sp_dboption '<dbname>', 'single user', TRUE
exec sp_fixindex '<indexname>', sysfilegroups, 1
exec sp_dboption '<dbname>', 'single user', FALSE
You may want to try it on your sysforeignkeys table..
Rick Sawtell
MCT, MCSD, MCDBA