April 17, 2007 at 12:09 pm
I've written the following query to return a list of DBs, the dev and prod servers, and the contractor which is responsible for them. I based the responsible contractor on who has dbo access to the db. There are 124 DBs total but the script is only returning 104, and many are repeated. Could anyone who is good with JOINs tell me what I am doing wrong? Thanks!
SELECTDBs.DBName,
ServersDev.ServerName AS DevServerName,
ServersProd.ServerName AS ProdServerName,
Agencies.AgName AS Contractor
FROMDBs INNER JOIN Servers ServersDev
ONDBs.DBDevServer = ServersDev.ServerID INNER JOIN Servers ServersProd
ONDBs.DBProdServer = ServersProd.ServerID,
DBAccounts INNER JOIN Members
ONDBAccounts.MemberID = Members.MemberID INNER JOIN Agencies
ONMembers.Agency = Agencies.AgID
WHEREDBs.Status = 1
ANDDBAccounts.Accesstype = 'dbo'
ANDDBs.DBID = DBAccounts.DBID
ORDER BY DBs.DBName
April 17, 2007 at 1:08 pm
I'm guessing that some of your DBs are missing either server or member entries, so change your INNER JOINs to LEFT OUTER JOINS, and see if that's the results you are looking for.
April 18, 2007 at 10:08 am
Thanks for your reply. I modified the script to this and now I get 126 rows returned, but many of the DBs are still listed twice, where as they should only be listed once. Still puzzled.
SELECTDBs.DBName,
ServersDev.ServerName AS DevServerName,
ServersProd.ServerName AS ProdServerName,
Agencies.AgName AS Contractor
FROMDBs LEFT OUTER JOIN Servers ServersDev
ONDBs.DBDevServer = ServersDev.ServerID LEFT OUTER JOIN Servers ServersProd
ONDBs.DBProdServer = ServersProd.ServerID,
DBAccounts JOIN Members
ONDBAccounts.MemberID = Members.MemberID JOIN Agencies
ONMembers.Agency = Agencies.AgID
WHEREDBs.Status = 1
ANDDBAccounts.Accesstype = 'dbo'
ANDDBAccounts.DBID = DBs.DBID
ORDER BY DBs.DBName
April 18, 2007 at 11:41 am
I solved the problem by selecting DISTINCT DBs and eliminating the AND statement in the WHERE clause (DBAccounts.Accesstype = 'dbo'). Thanks.
SELECTDistinct DBs.DBName,
ServersDev.ServerName AS DevServerName,
ServersProd.ServerName AS ProdServerName,
Agencies.AgName AS Contractor
FROMDBs INNER JOIN Servers ServersDev
ONDBs.DBDevServer = ServersDev.ServerID INNER JOIN Servers ServersProd
ONDBs.DBProdServer = ServersProd.ServerID,
DBAccounts JOIN Members
ONDBAccounts.MemberID = Members.MemberID JOIN Agencies
ONMembers.Agency = Agencies.AgID
WHEREDBs.Status = 1
ANDDBs.DBID = DBAccounts.DBID
ORDER BY DBs.DBName
April 18, 2007 at 12:28 pm
By the way, the fact that you get duplicates means that you have some DBs that have either multiple Servers or multiple Members. While the DISTINCT takes care of it here, if that's not supposed to happen, you might want to delve a bit further and see what's happening.
April 18, 2007 at 12:37 pm
That's correct. There are multiple members (contractors) who have access to the DBs. Using DISTINCT allowed me to include them all in the list, so some DBs show up more than once which is ok. Thanks again.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply