best query to get data from multiple tables

Question

best query to get data from multiple tables

crisgomez08

Newbie

Points: 7
More actions
June 8, 2010 at 2:12 am

#223563

Hi ,
Can anyone help me regarding what is the best way to get the records in a multiple tables.
Here are the tables and its relationship:
Users -1 to 1 relatioship->ApplicantDetails
The relationship of Users with the rest of the tables below is 1-To- many relationship
Users(Id,Firstname,Lastname,Middlename,AlternativeEmail,Email)
ApplicantDetails(Id,uId,Address1,Address2,City,Province,StateorRegion)
Certification(Id,uId,AlternativeEmail,Email,Firstname,Lastname,Middlename)
EducationalBackground(Id, uId,fieldofStudy,other,School)
EmploymentDetails (Id, uId,Address1,Address2,City,CompanyName,DescriptionofDuties,Position,Province,TypeofBusiness)
Expertise(Id, uId,Expertise)
GeographicalExperience(Id, uId,Description)
ProjectDetails(Id, uId,Address1,Address2,City,ProjectDescription,Projectname,Projectrole,Province,ServiceRendered)
What is the best query to get the data of multiple records without having a duplicate of Users data?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

The best way? Impossible to tell from what you've provided. Which output columns do you want, and from which tables? Build a SELECT...FROM...with a modest restriction and test it. Look at the execution plan. Look at your joins, check indexes on the tables. Eyeball the data with each change of the code. Measure potential dupes with a targeted GROUP BY of an "onion" select of your query. Most important of all, read your course notes and ensure you have correctly interpreted the question.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

I agree with Chris that it depends. Furthermore, any non-XML result set that displays the user data will have to duplicate the user data if more than one row is returned for user. Probably time to go back and get clarification of your requirements.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

crisgomez08 Newbie Points: 7 More actions · Answer 3

Hi,

Sorry I am new here.Thanks for the link at least I know the "Best Practices" to post question here.Thanks a lot.;-)