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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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)
 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
2241 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  
 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.05 seconds. Powered By: Snitz Forums 2000