SQL Server T-SQL – Single Query to Get Data from Multiple Tables

sql servert-sql

I have 36 Microsoft SQL Server VM's, each with a specific database ending in _Sec as the DB name. There are 2 tables in this database that I need to get data from in a single query: tblConnGroups and tblConnGroupsDet.

tblConnGroups has 3 columns that I need to include in the results

tblConnGroups.ID
tblConnGroups.GroupName
tblConnGroups.Provisional

tblConnGroupsDet has essentially one column that is needed in the results.

tblConnGroupsDet.GroupID
tblConnGroupsDet.DBName

tblConnGroups.ID is a key used in tblConnGroupsDet.GroupID, however in tblConnGroups the ID field has a one-to-many relationship to the GroupID field in tblConnGroupsDet.

The Connection Group Name only appears in the tblConnGroups with its ID, and other information, but the GroupID will be in each row of the tblConnGroupsDet with a member database name.

USE [Name_Sec];
GO
SELECT ID,Groupname,Provisional 
from [tblConnGroups] 
ORDER BY ID ASC

ID   GroupName       Provisional
514  Name_FY0910     0
844  Name_FY1011     0
1588 NameTest_FY1516 1

USE [Name_Sec];
GO
SELECT GroupID,DBName 
FROM [tblConnGroupsDet] 
ORDER BY GroupID ASC

GroupID  DBNAME
514      Name_Base
514      Name_FY0910
514      Name_State0910
514      Name_portal
514      Name_FY1011

I tried a couple of different join queries to match the tblConnGroups.ID and tblConnGroupsDet.GroupID and haven't quite figured out how to get the information I want out of these two tables.

Basically I need a query that will give me these results where GroupName will get repeated for each DBName where the ID/GroupID match:

GroupName,DBName,FileSystemPath

I'm hopeful that this is clearer than mud, I don't do a whole lot of DB Admin work, we've been trying to find a DBA, and this is a hat I don't put on very often.

Best Answer

SSMS has a feature called Server Groups, which allows exactly that functionality.

As in the above image, add a new Server Group, then add each target SQL Server to the group.

Then right-click the newly created group, and choose "New Query". Enter the following into the query window:

DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = @cmd + 'USE ' + QUOTENAME(d.name) + ';
SELECT SECDB = ''' + d.name + '''
    , g.*
    , gd.*
FROM dbo.tblConnGroups g
    INNER JOIN dbo.tblConnGroupsDet gd ON g.ID = gd.GroupID
ORDER BY gd.GroupID, gd.DBName;
'
FROM sys.databases d
WHERE d.name LIKE '%_Sec';

EXEC sys.sp_executesql @cmd;

The code above will dynamically execute your query against any/all databases named like *_Sec on all SQL Servers in the group.

Related Solutions

Sql-server – Grouping results to get unique rows after multiple joins

If you just want to return one row per story rather than a set, you can use MIN() or MAX() (and a GROUP BY) or SQL Server's TOP 1 function within a subquery to do this. See https://stackoverflow.com/questions/4229931/how-to-write-a-query-to-get-only-first-matching-row-while-joining-two-tables .

Without sample data, and with that complex a query, I don't trust myself to write out bug-free code for you, but you should be able to work it out yourself using the technique described in that article. If not, indicate where you get stuck.

Combine Columns from Multiple Rows into Single Row in SQL Server

This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:

DECLARE @x TABLE 
(
  id INT, 
  row_num INT, 
  customer_code VARCHAR(32), 
  comments VARCHAR(32)
);

INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';

SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments 
    FROM @x AS x2 WHERE id = x.id
     ORDER BY row_num
     FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;

If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:

     FOR XML PATH('')), 1, 1, '')

To this more elaborate approach:

     FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')

(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)

Best Answer

Related Solutions

Sql-server – Grouping results to get unique rows after multiple joins

Combine Columns from Multiple Rows into Single Row in SQL Server

Related Question