INNER JOIN on Multiple columns

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

SQL Server 2000 Forums

SQL Server Development (2000)

INNER JOIN on Multiple columns

Author

Topic

ElCapitan
Starting Member

28 Posts

Posted - 2009-03-24 : 09:40:53

Hi all,
I need to find out if doing an INNER JOIN on multiple columns is a legitimate way of grabbing data.

I have a User table (tblUser)

tblUser
------------
UserID
UserTypeID
MasterClubID
SiteSkinID
DateJoined

I need to find out who has the same values across three columns for Users' who joined before a certain date.

SELECT * FROM tblUser u1
INNER JOIN tblUser u2 ON u2.UserTypeID=u1.UserTypeID
AND u2.MasterClubID=u1.MasterClubID
AND u2.SiteSkinID=u1.SiteSkinID
WHERE u1.DateJoined<=01/01/2006

Is multiple column joins a legitimate way of grabbing data? Does it have any significant impact on performance?

Thanks for you time.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-03-25 : 09:41:57

quote:
Originally posted by ElCapitan

Is multiple column joins a legitimate way of grabbing data?

Absolutely. If it's appropriate to fulfill your requirements it should narrow down the returned dataset.

quote:
Originally posted by ElCapitan

Does it have any significant impact on performance?

The dreaded "It depends". Are your indexes appropriate for the join? Are they up-to-date i.e. little fragmentation, statistics updated recently? How much data is contained in the 2 tables? Check out the execution plan for more info as well.

Now, as far as your posted query returning the result set that you seem to want, I don't believe it will - but give it a go and see what you get. If it does not work as expected, post some sample data and what the expected results should be and we can help.

Terry

-- Procrastinate now!

ElCapitan
Starting Member

28 Posts

Posted - 2009-03-25 : 11:39:24

Thanks for your reply. If it is a legit way of grabbing data then I am happy to continue with designing the schema of the database knowing that it is ok (in this one circumstance) to get data this way. The query I showed was just a sample to demonstrate my intention. Thank you for confirming that type of query is ok even though in the example not quite realistic.

I shall post back to this thread if I have any other questions if you could subscribe to this topic. Thanks again for your time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources