SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 I cannot figure out this join (3 tables)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Tomji
Starting Member

19 Posts

Posted - 04/02/2013 :  09:14:03  Show Profile  Reply with Quote
Available

PRB0000A	Office	   4	172.20.20.0
PRB0000B	Windows    5	172.40.40.0

Packages

PRB0000A	Windows
PRB0000B	Office
PRB0000C	Tools	

Subnets

172.20.20.0
172.40.40.0
172.50.50.0


Desired Table result

PRB0000A	Office	   	4	172.20.20.0
PRB0000A	Office	   	0	172.40.40.0
PRB0000A	Office		0	172.50.50.0
PRB0000B	Windows 	0	172.20.20.0
PRB0000B	Windows		5	172.40.40.0
PRB0000B	Windows		0	172.50.50.0
PRB0000C	Tools 	   	0	172.20.20.0
PRB0000C	Tools      	0	172.40.40.0
PRB0000C	Tools      	0	172.50.50.0


Here is a quick T-SQL to create these 3 tables.
DECLARE @Available TABLE (Package VARCHAR(20), Name VARCHAR(20), Avail INT, Subnet VARCHAR(20))
INSERT INTO @Available
SELECT
'PRB0000A','Office',4,'172.20.20.0'
UNION ALL
SELECT
'PRB0000B','Windows',5,'172.40.40.0'

DECLARE @Packages TABLE (Package VARCHAR(20), Name VARCHAR(20))
INSERT INTO @Packages
SELECT
'PRB0000A','Windows'
UNION ALL
SELECT
'PRB0000B','Office'
UNION ALL
SELECT
'PRB0000C','Tools'	

DECLARE @Subnets TABLE (Subnet VARCHAR(20))
INSERT INTO @Subnets
SELECT
'172.20.20.0'
UNION ALL
SELECT
'172.40.40.0'
UNION ALL
SELECT
'172.50.50.0'

Edited by - Tomji on 04/02/2013 09:18:13

Tomji
Starting Member

19 Posts

Posted - 04/02/2013 :  09:47:22  Show Profile  Reply with Quote
Looks like I am very close with my own solution. Will post it soon.
Go to Top of Page

Tomji
Starting Member

19 Posts

Posted - 04/02/2013 :  09:51:59  Show Profile  Reply with Quote
Got it:

DECLARE @PackagesSubnets TABLE (Package VARCHAR(20),Name VARCHAR(20),Subnet VARCHAR(20),Avail INT)
INSERT INTO @PackagesSubnets
SELECT Package,Name,Subnet,0 AS Avail FROM @Packages pkg
OUTER APPLY @Subnets subnets

SELECT ISNULL(avail.Package,pknets.Package) AS Package,ISNULL(avail.Name,pknets.Name) AS Name,ISNULL(avail.Avail,pknets.Avail) AS Avail,ISNULL(avail.Subnet,pknets.Subnet) AS Subnet FROM
@PackagesSubnets pknets
LEFT JOIN @Available avail ON pknets.Package = avail.Package AND pknets.Subnet = avail.Subnet


Basically first I create a join table that contains every possible subnet and package combination. Then I simply LEFT join the "real" table.
Way easier than I initially thought. I honestly think creating a small example like I did here helps a lot with the thought processes.

Edited by - Tomji on 04/02/2013 09:52:26
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/02/2013 :  10:00:43  Show Profile  Reply with Quote
--Alternate

SELECT p.Package, p.Name, p.Subnet, COALESCE(a.Avail, 0) Avail
FROM (SELECT b.Package, s.Subnet, b.Name 
          FROM @Subnets s ,@Packages b
      ) p 
LEFT JOIN @Available a ON a.Subnet = p.Subnet AND a.Name = p.Name
Go to Top of Page

Tomji
Starting Member

19 Posts

Posted - 04/02/2013 :  10:17:04  Show Profile  Reply with Quote
Hi bandi,

Yes, that works just as well and is the same overall idea. I think the 3 tables threw me off.
I will tun these again a larger data set soon and will simply take the faster one, I suspect yours will be faster due to less transactions.
Cheers.

Edited by - Tomji on 04/02/2013 13:20:53
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000