CODEWITHSUNDEEP
sqlsql-servert-sql
YoungSQLee
YoungSQLee

Reputation: 9

Connecting to tables from different servers

i need some help...

i have 2 inhouse sql servers, one houses the student information and the second one has the inventory.

what i would like to do is to link the two together and get the extra tables from the "userfile" server.

below is part of the sql that i extracted from the student information and i know it works.

fyi, the first part of sql works - i do get all the tables from the first part of the SQL but none of the second part (AND PupilPersonalDetails.PupilID inner join [MYSERVERNAME].USERFILE.dbo.pupil on PupilID = user_file_userid)

Thanks in adavnce for your help

SELECT
  CurrentPupil.SchoolID AS 'SchoolID',
  CurrentPupil.PupilID AS
  'PupilID',
  PupilPersonalDetails.Surname AS 'Surname',
  PupilPersonalDetails.Forename AS 'First_Name',
  PupilCurrentSchoolYearGroupLookupDetails.Description AS 'Year_Group'
FROM CurrentPupil
LEFT OUTER JOIN PupilPersonalDetails AS PupilPersonalDetails
  ON CurrentPupil.SchoolID = PupilPersonalDetails.SchoolID
  AND CurrentPupil.PupilID = PupilPersonalDetails.PupilID
LEFT OUTER JOIN PupilCurrentSchool AS
PupilCurrentSchool
  ON CurrentPupil.SchoolID = PupilCurrentSchool.SchoolID
  AND CurrentPupil.PupilID = PupilCurrentSchool.PupilID
LEFT OUTER JOIN LookupDetails
AS PupilCurrentSchoolYearGroupLookupDetails
  ON PupilCurrentSchoolYearGroupLookupDetails.LookupDetailsID =
  PupilCurrentSchool.YearGroup
  AND PupilCurrentSchoolYearGroupLookupDetails.LookupID = '3004'
  AND PupilPersonalDetails.PupilID
INNER JOIN [win2k12sql].USERFILE.dbo.pupil
  ON PupilID = user_file_userid
WHERE ('%wc%' = '%wc%')

Upvotes: 1

Views: 100

Answers (2)

Sean Lange
Sean Lange

Reputation: 33571

Your queries would be a lot easier to work with if you used some aliases. To be fair you had a lot of aliases in your code but the alias was the same as the table name. That is like saying my name is "Sean Lange" also known as "Sean Lange". I like to use pretty short alias names and then keep them consistent between queries as much as possible.

Also don't use string literals as alias names. It will work but it is adding confusion, more keystrokes and zero benefit. And no need for an alias on a column unless you want the name of the column to be different in the result set.

With just some aliases and formatting your wall of text query becomes quite easy to see what is going on. All tidied up your query might look something like this.

SELECT
    cp.SchoolID
    , cp.PupilID
    , ppd.Surname
    , ppd.Forename AS First_Name
    , pcsygld.Description AS Year_Group
FROM CurrentPupil cp
LEFT OUTER JOIN PupilPersonalDetails AS ppd ON cp.SchoolID = ppd.SchoolID 
                                        AND cp.PupilID = ppd.PupilID
LEFT OUTER JOIN PupilCurrentSchool AS pcs ON cp.SchoolID = pcs.SchoolID 
                                        AND cp.PupilID = pcs.PupilID
LEFT OUTER JOIN LookupDetails AS pcsygld ON pcsygld.LookupDetailsID = pcs.YearGroup
                                        AND pcsygld.LookupID = '3004'
                                        AND ppd.PupilID = cp.PupilID --you were missing cp.PupilID
INNER JOIN win2k12sql.USERFILE.dbo.pupil p ON p.PupilID = user_file_userid
WHERE ('%wc%' = '%wc%') --This doesn't make any sense. You are comparing two string literals.

Upvotes: 1

MichaelEvanchik
MichaelEvanchik

Reputation: 1766

please write it clean like this

and please test the following

select * from [win2k12sql.USERFILE.dbo.pupil where pupilID = NumberYouKnowExists where ('%wc%' = '%wc%')

SELECT CurrentPupil.SchoolID as 'SchoolID', CurrentPupil.PupilID as 
'PupilID', PupilPersonalDetails.Surname as 'Surname', 
PupilPersonalDetails.Forename as 'First_Name',
PupilCurrentSchoolYearGroupLookupDetails.Description as 'Year_Group' 

FROM CurrentPupil 

LEFT OUTER JOIN PupilPersonalDetails AS PupilPersonalDetails ON
 CurrentPupil.SchoolID = PupilPersonalDetails.SchoolID AND 
CurrentPupil.PupilID = PupilPersonalDetails.PupilID 

LEFT OUTER JOIN PupilCurrentSchool AS 
PupilCurrentSchool ON CurrentPupil.SchoolID = PupilCurrentSchool.SchoolID 

AND 

CurrentPupil.PupilID = PupilCurrentSchool.PupilID 

LEFT OUTER JOIN LookupDetails  AS PupilCurrentSchoolYearGroupLookupDetails 
ON 
PupilCurrentSchoolYearGroupLookupDetails.LookupDetailsID = 
PupilCurrentSchool.YearGroup 

AND 

PupilCurrentSchoolYearGroupLookupDetails.LookupID = '3004' 

AND

 PupilPersonalDetails.PupilID    --do you have an issue here?

inner join [win2k12sql].USERFILE.dbo.pupil on 
PupilID = user_file_userid  

 WHERE ('%wc%' = '%wc%')

Upvotes: 1

Related Questions