I cannot figure out this join (3 tables)

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)

I cannot figure out this join (3 tables)

Author

Topic

Tomji
Starting Member

19 Posts

Posted - 2013-04-02 : 09:14:03

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'

Tomji
Starting Member

19 Posts

Posted - 2013-04-02 : 09:47:22

Looks like I am very close with my own solution. Will post it soon.

Tomji
Starting Member

19 Posts

Posted - 2013-04-02 : 09:51:59

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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-02 : 10:00:43

--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

Tomji
Starting Member

19 Posts

Posted - 2013-04-02 : 10:17:04

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources