SELECT		tm.Id,
		tm.TestCompanyID,
		d.TestCompanyID,
		d.Id
FROM		TestMain tm
LEFT JOIN	(
			SELECT	TestCompanyID,
				Id
			FROM	TestCompany1
			UNION ALL
			SELECT	TestCompanyID,
				Id
			FROM	TestCompany2
			UNION ALL
			SELECT	TestCompanyID,
				Id
			FROM	TestCompany3
		) d ON d.TestCompanyID = tm.TestCompanyID

CREATE TABLE #TESTMAIN
(TESTMAINID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY1
(ID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY2
(ID INT , TESTCOMPANYID INT )

CREATE TABLE #TESTCOMPANY3
(ID INT , TESTCOMPANYID INT )

INSERT #TESTMAIN
SELECT 1, 1000 UNION ALL
SELECT 2, 2000 UNION ALL
SELECT 3, 3000 UNION ALL
SELECT 4, 4000 UNION ALL
SELECT 5, 5000 UNION ALL
SELECT 6, 6000 UNION ALL
SELECT 7, 7000

INSERT #TESTCOMPANY1
SELECT 1, 1000 UNION ALL
SELECT 2, 6000


INSERT #TESTCOMPANY2
SELECT 1, 3000 UNION ALL
SELECT 2, 4000 UNION ALL
SELECT 3, 5000

INSERT #TESTCOMPANY3
SELECT 1, 7000

SELECT X1.TESTMAINID, X1.TESTCOMPANYID, ISNULL(X2.ID,0), ISNULL(X3.ID,0), ISNULL(X4.ID,0)
FROM #TESTMAIN X1
LEFT JOIN #TESTCOMPANY1 X2 ON X1.TESTCOMPANYID = X2.TESTCOMPANYID
LEFT JOIN #TESTCOMPANY2 X3 ON X1.TESTCOMPANYID = X3.TESTCOMPANYID
LEFT JOIN #TESTCOMPANY3 X4 ON X1.TESTCOMPANYID = X4.TESTCOMPANYID

quote:

---

Originally posted by Peso

I'm lucky?


Peter Larsson
Helsingborg, Sweden

---