SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 INNER JOIN on Multiple columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ElCapitan
Starting Member

28 Posts

Posted - 03/24/2009 :  09:40:53  Show Profile  Reply with Quote
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

USA
676 Posts

Posted - 03/25/2009 :  09:41:57  Show Profile  Reply with Quote
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!

Edited by - tosscrosby on 03/25/2009 09:47:26
Go to Top of Page

ElCapitan
Starting Member

28 Posts

Posted - 03/25/2009 :  11:39:24  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000