Conditional Joins

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,

HHADo you have defined any relationship between the tables..? It's basic requirement for data integrity.

Anyway you can join the two tables this way...

Select Testmain.TestMainId, TestMain.TestCompanyID From TestMain
JOIN TestCompany1 ON TestMain.TestCompanyID = TestCompany1.TestCompanyID

You can join more than two tables using different join types...|||Hi

You would need to use left joins and maybe COALESCE but it is hard to know without more details. The fact that you are doing this hints that your design may not be sound too (although it may be - this looks like a mock up yes?).

HTH|||I think your best bet would be to inner join against each table and UNION or UNION ALL the results - your design does look dubious though, why are you segmenting companies across 3 tables - do they have different attributes per collection or is there another reason?|||Hi all,

There is a main table say COMPANY and they other tables CompanyA , CompanyB , CompanyC.

The main table COMPANY has the general info about the company ( like address , contact info) and there are 3 BIT columns to indicate what all type of company it falls under.If it falls under A & B , then the relevant information
are stored in CompanyA & CompanyB.

Now I need to write a proc which gets few input parameters and searches
for the company details.

1. If no parameters where passed , I need to get all the company from the
COMPANY with relevant information from the CompanyA, CompanyB,CompanyC.

2.If I get a parameter which says I should fetch only companys falling under
CompanyA , I should be able to get them too.

Still , the DB is in production , I cant touch the design.

Thanks for all your help ,

HHA|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=71565

Terrible design - I'm sure you know or are at least becoming aware of.

Playing with post three from the link (not efficient - there may be better solutions):

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

DECLARE @.CompanyOneOnly AS Bit
SET @.CompanyOneOnly = 1

SELECT *
FROM -- Relvent companys
(SELECT X1.TESTMAINID,
X1.TESTCOMPANYID,
ISNULL(X2.ID,0) AS Comp1,
ISNULL(X3.ID,0) AS Comp2,
ISNULL(X4.ID,0) AS Comp3
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) AS DerT
WHERE CAST(Comp1 AS Bit) = @.CompanyOneOnly OR @.CompanyOneOnly = 0

DROP TABLE #TESTMAIN
DROP TABLE #TESTCOMPANY1
DROP TABLE #TESTCOMPANY2
DROP TABLE #TESTCOMPANY3