How to join multiple columns to one column

  • Hello ALL,

    I have 3 tables and each table has 3 columns. For example,

    table 1:

    DDS CTCLEARED CTCLEAREDDATE

    CT 3 1/2/2009

    CT 5 1/5/2009

    table 2:

    DDS BOCLEARED BOCLEAREDDATE

    BO 2 1/3/2009

    BO 1 1/5/2009

    Table 3:

    DDS NHCLEARED NHCLEAREDDATE

    NH 4 1/3/2009

    NH 5 1/8/2009

    Table 4 is the table of the sum of all tables and I called that table is Total. I have joined three tables into one table (In View)

    ToTalCleared ClearedDate

    3 1/2/2009

    6 1/3/2009

    6 1/5/2009

    5 1/8/2009

    Below is my script for the "Total" table

    Select dbo.CTCleared.CTCleared, dbo.BOCleared.BOCleared, dbo.NHCleared.NHCleared, dbo.TotalCleared.TotalCleared, dbo.TotalCleared.ClearedDate

    From dbo.CTCleared FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.CTClearedDate = dbo.TotalCleared.ClearedDate FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.BOClearedDate = dbo.TotalCleared.ClearedDate

    FULL OUTER JOIN dbo.NHCleared on

    dbo.NHCleared.NHClearedDate = dbo.TotalCleared.ClearedDate

    Then I got the result that I wish to have

    CTCleared BOCleared NHCleared TotalCleared ClearedDate

    3 0 0 3 1/2/2009

    0 2 4 6 1/3/2009

    5 1 0 6 1/5/2009

    0 0 5 5 1/8/2009

    But I forgot to join the DDS column of each table to the DDS of the "Total" table.

    Then I added the following codes in order to join DDS column of each table to DDS of Total table:

    Select dbo.CTCleared.CTCleared, dbo.BOCleared.BOCleared, dbo.NHCleared.NHCleared, dbo.TotalCleared.TotalCleared, dbo.TotalCleared.ClearedDate, dbo.TotalCleared.DDS

    From dbo.CTCleared FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.CTClearedDate = dbo.TotalCleared.ClearedDate FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.BOClearedDate = dbo.TotalCleared.ClearedDate

    FULL OUTER JOIN dbo.NHCleared on

    dbo.NHCleared.NHClearedDate = dbo.TotalCleared.ClearedDate

    AND

    dbo.CTCleared FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.DDS = dbo.TotalCleared.DDS FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.DDS = dbo.TotalCleared.DDS

    FULL OUTER JOIN dbo.NHCleared on

    dbo.NHCleared.DDS = dbo.TotalCleared.DDS

    It gives me a syntax error and that error message is: "An expression of non-boolean type specified in a context where a condition is expected, near 'FULL' "

    Would you please tell me how to fix this error?

    Thank you so much

    Joe

  • That statement compiles for me (no idea if it's doing what you want). I'm guessing it's a copy and paste error or you're missing something like : "= sometable.somecolumn"

    Select dbo.CTCleared.CTCleared, dbo.BOCleared.BOCleared, dbo.NHCleared.NHCleared, dbo.TotalCleared.TotalCleared, dbo.TotalCleared.ClearedDate, dbo.TotalCleared.DDS

    From dbo.CTCleared FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.CTClearedDate = dbo.TotalCleared.ClearedDate FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.BOClearedDate = dbo.TotalCleared.ClearedDate

    FULL OUTER JOIN dbo.NHCleared on

    dbo.NHCleared.NHClearedDate = dbo.TotalCleared.ClearedDate

    --AND dbo.CTCleared

    FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.DDS = dbo.TotalCleared.DDS FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.DDS = dbo.TotalCleared.DDS

    FULL OUTER JOIN dbo.NHCleared on

    dbo.NHCleared.DDS = dbo.TotalCleared.DDS

  • josephptran2002 (1/29/2009)

    From dbo.CTCleared FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.CTClearedDate = dbo.TotalCleared.ClearedDate FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.BOClearedDate = dbo.TotalCleared.ClearedDate

    FULL OUTER JOIN dbo.NHCleared on

    dbo.NHCleared.NHClearedDate = dbo.TotalCleared.ClearedDate

    AND

    dbo.CTCleared FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.DDS = dbo.TotalCleared.DDS FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.DDS = dbo.TotalCleared.DDS

    FULL OUTER JOIN dbo.NHCleared on

    dbo.NHCleared.DDS = dbo.TotalCleared.DDS

    Change you query as below

    From dbo.CTCleared

    FULL OUTER JOIN dbo.TotalCleared on dbo.CTCleared.CTClearedDate = dbo.TotalCleared.ClearedDate

    AND dbo.CTCleared.DDS = dbo.TotalCleared.DDS

    FULL OUTER JOIN dbo.BOCleared on dbo.BOCleared.BOClearedDate = dbo.TotalCleared.ClearedDate

    AND dbo.BOCleared.DDS = dbo.TotalCleared.DDS

    FULL OUTER JOIN dbo.NHCleared on dbo.NHCleared.NHClearedDate = dbo.TotalCleared.ClearedDate

    AND dbo.NHCleared.DDS = dbo.TotalCleared.DDS

    Regards,
    Nitin

  • I recommend you do a few things:

    - Is it really necessary to join 7 tables all using FULL OUTER JOIN? It's really bad database design if that's the case. Most designs have one (or maybe two) rather central table where you link all other tables on.

    - Good SQL design is to give an alias to all tables. If you do that, you'll see you have in one join 'AND dbo.CTCleared' left alone. In fact, in this case it's needed, because you join all the tables twice in one query.

    Here I'll give an example how the tables could be joined, I left the SELECT clause, because from your query, I have no idea from which table you want the items.FROM dbo.CTCleared CTC

    INNER JOIN dbo.TotalCleared TC1 on TC1.CTClearedDate = CTC.ClearedDate

    INNER JOIN dbo.BOCleared BOC1 on BOC1.BOClearedDate = CTC.ClearedDate

    INNER JOIN dbo.NHCleared NCH1 on NCH1.NHClearedDate = CTC.ClearedDate

    INNER JOIN dbo.TotalCleared TC2 on TC2.DDS = CTC.DDS

    INNER JOIN dbo.BOCleared BOC2 on BOC2.DDS = CTC.DDS

    INNER JOIN dbo.NHCleared NCH2 on BOC2.DDS = CTC.DDSIf all ClearedDate and DDS items exist in all the tables, you can keep this. If they all exist in CTCleared, you make LEFT OUTER JOINs. Other than that, I try to avoid...

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

  • I just build a massive report like this.... you knoq the kind where they want to see everything for every part of the business on the same report.

    The only way out for me was a full outer join because of depreciated widget codes (no longer in existence anywhere except history of orders).

    Basically this is the only way out without full joins :

    SELECT dt from dbo.Calendar where dt between @BeginDate and @EndDate

    LEFT OUTER JOIN tbl1

    LEFT OUTER JOIN tbl2

    LEFT OUTER JOIN tbl3

    LEFT OUTER JOIN tbl4

    LEFT OUTER JOIN tbl5

    Where dbo.Calendor holds all the dates for a couple calendar years (mine holds them from 2000 to 2040)...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply