SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Conditional Joins
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Hariarul
Posting Yak Master

India
160 Posts

Posted - 09/06/2006 :  05:46:12  Show Profile
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

Sweden
30282 Posts

Posted - 09/06/2006 :  05:54:00  Show Profile  Visit SwePeso's Homepage
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 09/06/2006 :  05:56:09  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
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



Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 09/06/2006 :  06:00:02  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message
Damn!!!

Peso, How could you think so fast and still better???

Harsh Athalye
India.
"Nothing is Impossible"

Edited by - harsh_athalye on 09/06/2006 06:00:25
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 09/06/2006 :  06:29:59  Show Profile  Visit SwePeso's Homepage
I'm lucky?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Hariarul
Posting Yak Master

India
160 Posts

Posted - 09/06/2006 :  07:45:09  Show Profile
Thanks Everybody
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 09/06/2006 :  09:28:54  Show Profile  Send madhivanan a Yahoo! Message
quote:
Originally posted by Peso

I'm lucky?


Peter Larsson
Helsingborg, Sweden


Also clever

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000