Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    7

    Unanswered: 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,

    HHA

  2. #2
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Do 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...
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jun 2005
    Posts
    319
    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?

  5. #5
    Join Date
    Aug 2006
    Posts
    7
    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

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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):
    Code:
     
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •