Selecting multiple sublists that compose a more complex object in T-SQL

output.txt

(5 row(s) affected)
Id          Name      Network        Good
----------- --------- -------------- -----------
1           honestbot anarchyirc.com 0
2           derpbot   anarchyirc.com 1
3           logbot    anarchyirc.com 1
4           goat      anarchyirc.com 0
5           CHANFIX   efnet.org      1

(5 row(s) affected)

(3 row(s) affected)

Id          Name
----------- --------------
1           anarchyirc.com
2           efnet.org
3           freenode.net

(3 row(s) affected)

NetworkName    GoodBotName BadBotName
-------------- ----------- ----------
anarchyirc.com derpbot     NULL
anarchyirc.com logbot      NULL
anarchyirc.com NULL        goat
anarchyirc.com NULL        honestbot
efnet.org      CHANFIX     NULL
freenode.net   NULL        NULL

(6 row(s) affected)

query.sql

SELECT 1 Id, 'honestbot' Name, 'anarchyirc.com' Network, CAST(0 AS BIT) Good INTO #SubObject UNION SELECT 2, 'derpbot', 'anarchyirc.com', 1 UNION SELECT 3, 'logbot', 'anarchyirc.com', 1 UNION SELECT 4, 'goat', 'anarchyirc.com', 0 UNION SELECT 5, 'CHANFIX', 'efnet.org', 1;
SELECT * FROM #SubObject;
SELECT 1 Id, 'anarchyirc.com' Name INTO #ComplexObject UNION SELECT 2, 'efnet.org' UNION SELECT 3, 'freenode.net';
SELECT * FROM #ComplexObject;

-- Now get all the good and bad bots by networkish…?
SELECT
	 n.Name NetworkName
	,gb.Name GoodBotName
	,bb.Name BadBotName
FROM #ComplexObject n
LEFT JOIN (SELECT Id, Name, Network, Good FROM #SubObject UNION ALL SELECT NULL, NULL, Name, 1 FROM #ComplexObject) gb ON n.Name = gb.Network
LEFT JOIN (SELECT Name, Network, Good FROM #SubObject) bb ON n.Name = bb.Network AND gb.Id IS NULL
WHERE 1=1
AND gb.Good = 1
-- bb.Good IS NULL lets through all the gb lines. It also makes sure that freenode gets a line even
-- though it has no bots.
AND (bb.Good IS NULL OR bb.Good = 0)
-- Get consistent order from SQL Server so that we can read all GoodBotName and then all BadBotName.
ORDER BY n.Name, bb.Name, gb.Name;
GO
DROP TABLE #ComplexObject;
GO
DROP TABLE #SubObject;
GO