Inner Joins on Primary and Foreign Keys

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Inner Joins on Primary and Foreign Keys

Author

Topic

tcr18h
Starting Member

2 Posts

Posted - 2011-03-24 : 16:20:48

Hi. I'm very new to sql and need some help.

I'm joining 3 tables and seem to be getting duplicate rows in the result. Is this because I am joining a primary key to a primary key and should be joining a primary key to a foreign key?

Thanks

Here's an example of my code. The last join is primary to foreign.

Tables used are

Product with primary key PRID
Person with primary key PRID
Transaction with primary of PRDNUM

SELECT *

FROM Product

INNER JOIN Person
ON Product.PRID = Person.PRID

INNER JOIN Transaction
ON Person.PRDNUM = Transaction.PRDNUM

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-24 : 16:25:44

How do a person and a product relate? and why would they have matching PRIDs?...

I dunno, your example seems very confusing... how about some sample data and an expected result?

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

tcr18h
Starting Member

2 Posts

Posted - 2011-03-24 : 17:14:12

Hi Corey.

Thanks for the quick reply. I'm at home now so can't remember the exact tables.

I guess my question is can I join 2 tables where the common field is primary key in both tables.?

For example. Is the following a correct way to join?:

SELECT * FROM table1

INNER JOIN table2
ON table1.PrimaryKeyColumn = table2.PrimaryKeyColumn

Or is the following the correct way to join:

SELECT * FROM table1

INNER JOIN table2
ON table1.PrimaryKeyColumn = table2.ForeignKeyColumn

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-24 : 17:35:39

A column being a primary key isn't relevant to duplication of rows in a join. just not sure it makes a whole lot of sense.

Post sample data when you get back to work and we can help alot more.

Corey

snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-03-24 : 18:13:36

The correct way is to join columns that are related. If I have a table of cars and a table of people, then joining the primary key of cars (which identifies the car) to the primary key of the person table (which identifies the person) is not going to get me a list of who owns what car.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources