Conditional Joins - SQL Server Forums

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

Old Forums

CLOSED - General SQL Server

Conditional Joins

Author

Topic

Hariarul
Posting Yak Master

160 Posts

Posted - 2006-09-06 : 05:46:12

Hi all,

I have 4 tables with the structure shown below

Main Table :

Create Table TestMain
(TestMainId INT , TestCompanyID INT )

Other Tables :

Create Table TestCompany1
(Id INT , TestCompanyID INT )

Create Table TestCompany2
(Id INT , TestCompanyID INT )

Create Table TestCompany3
(Id INT , TestCompanyID INT )

In this above tables.. I would have a record in the table TestMain and a entry for that specific record would be in any of the tables like TestCompany1,TestCompany2,TestCompany3

Sample Records :

In the table TestMain

1 1000
2 2000
3 3000
4 4000
5 5000
6 6000
7 7000

In the table TestCompany1

1 1000
2 6000

In the table TestCompany2

1 3000
2 4000
3 5000

In the table TestCompany3

1 7000

How do I join those tables and fetch the main record with its subsequent entry from the other tables ?

Thanks in advance,

HHA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-06 : 05:54:00

Maybe this?

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

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-06 : 05:56:09

Use Left Join. Something like this...

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