Complex Problem: Joining two distinct databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Complex Problem: Joining two distinct databases

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2013-08-16 : 12:14:38

I need to join data from multiple databases (on the same server). That in itself doesn’t seem to be inherently difficult. However, I have a CTE that iterates down to get all the children of a specified group. Below I have two distinct queries. The first one has the proper lineage and the second one gets the proper data. What I don’t understand is how to combine these two using the lineage of the first query.

Query 1
This is the lineage I need (where f_locationID = some ID passed from the web app). This works as desired and gives me all the machines that belong to the location or machines that belong to children of the location. However, the data I need for these machines resides in another database entirely.

quote:

;with cte_assets as (
select a.f_locationid, a.f_locationparent, a.f_locationname, 0 as [lev], convert(varchar(30), '0_' + convert(varchar(10), f_locationid)) lineage
from [db_assets].[dbo].[tb_locations] a where f_locationID = '130' UNION ALL
select a.f_locationid
          ,a.f_locationparent
          ,a.f_locationname
          ,c.[lev] + 1
          ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_locationid))
   from   cte_assets c
   join   [db_assets].[dbo].[tb_locations] a
          on a.f_locationparent = c.f_locationID
 )
 select f_assetnetbiosname as 'Computer Name'
 from cte_assets c
 JOIN tb_assets ass on ass.f_assetlocation = c.f_locationID
 ORDER BY f_assetname DESC

Query 2

This is the data I need. The CTE here can be discarded as the lineage was set up merely as an example, and is not what I need. See the results window below to see the data. However, I’d like that data back out for the lineage from above (where ‘Computer Name’ from query 2 = ‘Computer Name’ from query 1).

quote:

;with rCTE as (
 	   select a.f_itemid
 			  ,a.f_itemparentid
 			  ,a.f_itemtype
  			  ,a.f_itemname
 			  ,0 as [lev]
 			  ,convert(varchar(30), '0_' + convert(varchar(10), f_itemid)) lineage
 	   from [db_reports].[dbo].[tb_locationsmachines] a
 	   where f_itemid = '1308' 
 	   UNION ALL
 	   select a.f_itemid
 			  ,a.f_itemparentid
 			  ,a.f_itemtype
 			  ,a.f_itemname
 			  ,c.[lev] + 1
 			  ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_itemid))
from   rCTE c
join   [db_reports].[dbo].[tb_locationsmachines] a
on a.f_itemparentid = c.f_itemid
)
--below is what I need, not the lineage above
SELECT f_computername as 'Computer Name', 
  SUM(f_sessionlength) AS 'Total Session Time', 
  COUNT(*) as 'Sessions', 
  COUNT(*)/60 as 'Average Sessions per Day', 
  CAST(SUM(f_sessionlength) / 3600E / COUNT(*) as DECIMAL(18,2)) as 'Average Session Length (Hours)'
from rCTE c
JOIN [db_reports].[dbo].[tb_sessions] ss on ss.f_computername = c.f_itemname
GROUP BY f_computername
Order By 'Sessions' DESC, f_computername ASC

The common field between the two tables is ‘Computer Name’. I think I need a CTE inside a CTE, but no matter what I try I cannot get it to work.

Any guidance is greatly appreciated, sincerely.

Thanks in advance,
Matt

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-16 : 12:23:42

I'm having a hard time following without sample data and expected output, but youc an "chain" CTEs together if that's what you are trying to do. For example, if you take your first sample and make the query that consumes teh cte a cte itself:

;with cte_assets as (
select a.f_locationid, a.f_locationparent, a.f_locationname, 0 as [lev], convert(varchar(30), '0_' + convert(varchar(10), f_locationid)) lineage
from [db_assets].[dbo].[tb_locations] a where f_locationID = '130' UNION ALL
select a.f_locationid
          ,a.f_locationparent
          ,a.f_locationname
          ,c.[lev] + 1
          ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_locationid))
   from   cte_assets c
   join   [db_assets].[dbo].[tb_locations] a
          on a.f_locationparent = c.f_locationID
 ),
 cte_a AS 
 (
	 select f_assetnetbiosname as 'Computer Name'
	 from cte_assets c
	 JOIN tb_assets ass on ass.f_assetlocation = c.f_locationID
	 ORDER BY f_assetname DESC
 ),
 ...

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2013-08-16 : 17:02:02

Thank you. It took me a while to compile a sample set of data, but here it is:

Script 1:

quote:

USE [_db_assets]
GO
/****** Object:  Table [dbo].[tb_locations]    Script Date: 08/16/2013 15:46:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tb_locations](
	[f_locationID] [int] IDENTITY(1,1) NOT NULL,
	[f_locationparent] [int] NULL,
	[f_locationname] [varchar](64) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tb_locations] ON
INSERT [dbo].[tb_locations] ([f_locationID], [f_locationparent], [f_locationname]) VALUES (7, 0, N'Memphis, TN')
INSERT [dbo].[tb_locations] ([f_locationID], [f_locationparent], [f_locationname]) VALUES (11, 7, N'Building 1')
INSERT [dbo].[tb_locations] ([f_locationID], [f_locationparent], [f_locationname]) VALUES (12, 7, N'Building 2')
INSERT [dbo].[tb_locations] ([f_locationID], [f_locationparent], [f_locationname]) VALUES (13, 7, N'Building 3')
SET IDENTITY_INSERT [dbo].[tb_locations] OFF
/****** Object:  Table [dbo].[tb_assets]    Script Date: 08/16/2013 15:46:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tb_assets](
	[f_assettagID] [int] IDENTITY(10001,1) NOT NULL,
	[f_assetnetbiosname] [varchar](36) NULL,
	[f_assetlocation] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tb_assets] ON
INSERT [dbo].[tb_assets] ([f_assettagID], [f_assetnetbiosname], [f_assetlocation]) VALUES (11001, N'MACHINE-A', 11)
INSERT [dbo].[tb_assets] ([f_assettagID], [f_assetnetbiosname], [f_assetlocation]) VALUES (11002, N'MACHINE-B', 12)
INSERT [dbo].[tb_assets] ([f_assettagID], [f_assetnetbiosname], [f_assetlocation]) VALUES (11003, N'MACHINE-C', 13)
INSERT [dbo].[tb_assets] ([f_assettagID], [f_assetnetbiosname], [f_assetlocation]) VALUES (11004, N'MACHINE-D', 13)
SET IDENTITY_INSERT [dbo].[tb_assets] OFF

Script 2:

quote:

USE [_db_reports]
GO
/****** Object:  Table [dbo].[tb_sessions]    Script Date: 08/16/2013 15:57:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tb_sessions](
	[f_ID] [int] IDENTITY(1,1) NOT NULL,
	[f_sessiondate] [datetime] NULL,
	[f_sessionlength] [int] NULL,
	[f_computername] [varchar](36) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tb_sessions] ON
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5560193, CAST(0x0000A21A00000000 AS DateTime), 2767, N'MACHINE-A')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5560079, CAST(0x0000A21A00000000 AS DateTime), 1884, N'MACHINE-D')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5559979, CAST(0x0000A21A00000000 AS DateTime), 388, N'MACHINE-A')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5560128, CAST(0x0000A21A00000000 AS DateTime), 2601, N'MACHINE-C')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563101, CAST(0x0000A21A00000000 AS DateTime), 1168, N'MACHINE-A')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563141, CAST(0x0000A21A00000000 AS DateTime), 327, N'MACHINE-B')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563175, CAST(0x0000A21A00000000 AS DateTime), 564, N'MACHINE-B')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5560943, CAST(0x0000A21A00000000 AS DateTime), 4515, N'MACHINE-D')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5560580, CAST(0x0000A21A00000000 AS DateTime), 5363, N'MACHINE-B')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5560831, CAST(0x0000A21A00000000 AS DateTime), 2694, N'MACHINE-A')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5562887, CAST(0x0000A21A00000000 AS DateTime), 3012, N'MACHINE-A')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563547, CAST(0x0000A21A00000000 AS DateTime), 4360, N'MACHINE-D')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5562701, CAST(0x0000A21A00000000 AS DateTime), 256, N'MACHINE-C')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563572, CAST(0x0000A21A00000000 AS DateTime), 224, N'MACHINE-D')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563641, CAST(0x0000A21A00000000 AS DateTime), 2575, N'MACHINE-C')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563679, CAST(0x0000A21A00000000 AS DateTime), 8980, N'MACHINE-B')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563807, CAST(0x0000A21A00000000 AS DateTime), 1128, N'MACHINE-C')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563831, CAST(0x0000A21A00000000 AS DateTime), 7051, N'MACHINE-D')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563755, CAST(0x0000A21A00000000 AS DateTime), 1845, N'MACHINE-C')
INSERT [dbo].[tb_sessions] ([f_ID], [f_sessiondate], [f_sessionlength], [f_computername]) VALUES (5563848, CAST(0x0000A21A00000000 AS DateTime), 2603, N'MACHINE-B')
SET IDENTITY_INSERT [dbo].[tb_sessions] OFF
/****** Object:  Table [dbo].[tb_locationsmachines]    Script Date: 08/16/2013 15:57:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_locationsmachines](
	[f_itemID] [int] IDENTITY(1,1) NOT NULL,
	[f_itemtype] [nvarchar](50) NULL,
	[f_itemparentID] [int] NOT NULL,
	[f_itemname] [nvarchar](50) NULL,
 CONSTRAINT [PK_tb_locationsmachines] PRIMARY KEY CLUSTERED 
(
	[f_itemID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tb_locationsmachines] ON
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1062, N'group', 1010, N'Memphis, TN')
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1067, N'machine', 1308, N'MACHINE-A')
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1068, N'machine', 1316, N'MACHINE-B')
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1069, N'machine', 1318, N'MACHINE-C')
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1070, N'machine', 1308, N'MACHINE-D')
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1308, N'group', 1062, N'Building 1')
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1316, N'group', 1062, N'Building 2')
INSERT [dbo].[tb_locationsmachines] ([f_itemID], [f_itemtype], [f_itemparentID], [f_itemname]) VALUES (1318, N'group', 1062, N'Building 3')
SET IDENTITY_INSERT [dbo].[tb_locationsmachines] OFF

Updated SQL to match:

quote:

--this is the lineage I need where f_locationID = some ID passed from the web app
--note the machines in the first result window are what I need data for
;with cte_assets as (
select a.f_locationid, a.f_locationparent, 0 as [lev], convert(varchar(30), '0_' + convert(varchar(10), f_locationid)) lineage
from [_db_assets].[dbo].[tb_locations] a where f_locationID = '7' UNION ALL
select a.f_locationid
          ,a.f_locationparent
          ,c.[lev] + 1
          ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_locationid))
   from   cte_assets c
   join   [_db_assets].[dbo].[tb_locations] a
          on a.f_locationparent = c.f_locationID
 )
 select f_assetnetbiosname as 'Computer Name'
 from cte_assets c
 JOIN [_db_assets].[dbo].[tb_assets] ass on ass.f_assetlocation = c.f_locationID
 ORDER BY f_assetnetbiosname ASC
 




--this is the data I need, so the lineage here is only for an example
;with rCTE as (
 	   select a.f_itemid
 			  ,a.f_itemparentid
 			  ,a.f_itemtype
  			  ,a.f_itemname
 			  ,0 as [lev]
 			  ,convert(varchar(30), '0_' + convert(varchar(10), f_itemid)) lineage
 	   from [_db_reports].[dbo].[tb_locationsmachines] a
 	   where f_itemid = '1062' 
 	   UNION ALL
 	   select a.f_itemid
 			  ,a.f_itemparentid
 			  ,a.f_itemtype
 			  ,a.f_itemname
 			  ,c.[lev] + 1
 			  ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_itemid))
from   rCTE c
join   [_db_reports].[dbo].[tb_locationsmachines] a
on a.f_itemparentid = c.f_itemid
)

SELECT f_computername as 'Computer Name', 
 SUM(f_sessionlength) AS 'Total Session Time', 
 COUNT(*) as 'Sessions', 
 COUNT(*)/60 as 'Average Sessions per Day', 
 CAST(SUM(f_sessionlength) / 3600E / COUNT(*) as DECIMAL(18,2)) as 'Average Session Length (Hours)'
from rCTE c
JOIN [_db_reports].[dbo].[tb_sessions] ss on ss.f_computername = c.f_itemname
where DATEDIFF(d, f_sessiondate, getdate()) < 60
GROUP BY f_computername
Order By f_computername ASC, 'Sessions' DESC

Again, thank you. Sincerely.

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2013-08-16 : 17:39:33

Using the scripts above to import a small sample set and using the script below those, the output should be as follows. I want only the bottom results (circled in red), but need to use the lineage/hierarchy from the top part of the sql. In other words, I need to join the "Session" data for the PCs in the second part of the query to the hierarchy from the first part of the query

Thanks,
Matt

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2013-08-16 : 17:55:23

I got it! I read your reply and just stopped trying to go down my own path and did as you said. Here is the resultant query that works!

quote:

;with cte_assets as (
select a.f_locationid, a.f_locationparent, 0 as [lev], convert(varchar(30), '0_' + convert(varchar(10), f_locationid)) lineage
from [_db_assets].[dbo].[tb_locations] a where f_locationID = '7' UNION ALL
select a.f_locationid
          ,a.f_locationparent
          ,c.[lev] + 1
          ,convert(varchar(30), lineage + '_' + convert(varchar(10), a.f_locationid))
   from   cte_assets c
   join   [_db_assets].[dbo].[tb_locations] a
          on a.f_locationparent = c.f_locationID
),
cte_a AS 
(
	select f_assetnetbiosname as 'Computer Name'
	from cte_assets c
	JOIN [_db_assets].[dbo].[tb_assets] ass on ass.f_assetlocation = c.f_locationID
)
 
SELECT [Computer Name] as 'Computer Name', 
 SUM(f_sessionlength) AS 'Total Session Time', 
COUNT(*) as 'Sessions', 
COUNT(*)/60 as 'Average Sessions per Day', 
CAST(SUM(f_sessionlength) / 3600E / COUNT(*) as DECIMAL(18,2)) as 'Average Session Length (Hours)'
from cte_a c
JOIN [_db_reports].[dbo].[tb_sessions] ss on ss.f_computername = c.[Computer Name]
GROUP BY [Computer Name]
Order By [Computer Name] ASC, 'Sessions' DESC

Thanks for the push off the cliff - I was off in left field myself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources