Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    33

    Unanswered: asking help on Full Outer Join on multi-tables

    Asking help from a challenge problem in SQL.

    Table a: Phone aMonth aCost Company
    1231231233 08/01 4.95 AA
    1231231233 07/01 4.95 AA
    ......
    Table b: Phone bMonth bCost Company
    1231231233 10/01 12.87 AA
    1231231233 09/01 13.87 AA
    1231231233 08/01 15.87 AA
    1231231233 07/01 17.87 AA
    1231231233 06/01 11.87 AA
    ......
    Table c: Phone cMonth cCost Company
    1231231233 10/01 1.00 AA
    1231231233 09/01 1.5 AA
    1231231233 08/01 1.5 AA
    1231231233 07/01 1.0 AA
    ......

    Now I need to FULL JOIN These three tables to get a view :
    My query in SQL is as following:

    Create View MonthView AS
    select
    case when
    (case when a.Phone is null then b.Phone else a.Phone end) is null then c.Phone else
    (case when a.Phone is null then b.Phone else a.Phone end) end as Phone,
    case when
    (case when a.aMonth is null then b.bMonth else a.aMonth end) is null then c.cMonth else
    (case when a.aMonth is null then b.bMonth else a.aMonth end) end as TranMonth,
    case when
    (case when a.company is null then b.company else a.company end) is null then c.company else
    (case when a.company is null then b.company else a.company end) end as company,
    a.aCost, b.bCost, c.cCost
    from a full JOIN b
    on a.Phone=b.Phone and a.aMonth=b.bMonth and a.company=b.company
    full JOIN c
    on a.Phone=c.Phone and a.aMonth=c.cMonth and a.company=c.company

    Suppose I will get a view like this:
    Phone TranMonth aCost bCost cCost Company
    1231231233 10/01 NULL 12.87 1.00 AA
    1231231233 09/01 NULL 13.87 1.50 AA
    1231231233 08/01 4.95 15.87 1.50 AA
    1231231233 07/01 4.95 17.87 1.00 AA
    1231231233 06/01 NULL 11.87 NULL AA


    However, I got a view as

    Phone TranMonth aCost bCost cCost Company
    1231231233 10/01 NULL NULL 1.00 AA
    1231231233 10/01 NULL 12.87 NULL AA
    1231231233 09/01 NULL 13.87 NULL AA
    1231231233 09/01 NULL NULL 1.50 AA
    1231231233 08/01 4.95 15.87 1.50 AA
    1231231233 07/01 4.95 17.87 1.00 AA
    1231231233 06/01 NULL 11.87 NULL AA


    I know that is because of the FULL JOIN of three tables with the case statement will cost this duplicated TranMonth problem. If I just bring down into 2 views which that the 1st view FULL
    JOIN two tables a and b, then the 2nd view FULL JOIN the 1st view and table c. However, I can't simply do that.

    I can't just change the
    table a FULL JOIN table c ON
    a.Phone=c.Phone and a.aMonth=c.cMonth and a.company=c.company
    into
    table b FULL JOIN table c ON
    b.Phone=c.Phone and b.bMonth=c.cMonth and b.company=c.company
    This will solve this example phone: 1231231233 but will cost the same problem with other phone numbers.

    What I need to do is modify the view and make it get the correct result. Is anyone can give me any piece of suggestion would be very very appreciated.

    Thank you very much in advanced.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If all you need is a hint, GROUP BY.

    Look at Coalesce() too, it won't fix any problems, but it will make the syntax a lot cleaner.

    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    33
    Thank Pat for replying.

    Could you talk more about the GROUP BY? And I will try to do more research by myself as well.

    I will post on my answer if I got any.

    Quote Originally Posted by Pat Phelan
    If all you need is a hint, GROUP BY.

    Look at Coalesce() too, it won't fix any problems, but it will make the syntax a lot cleaner.

    -PatP

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I could talk for hours about GROUP BY, but it would bore you to tears... I'll let you chew on this one a bit (a couple of hours), and if you don't have a solution by then I'll offer one. I'm betting that you find your own solution long before then though, because you seem to have a good grasp of the fundamentals and just needed a nudge in the right direction.

    -PatP

  5. #5
    Join Date
    Feb 2004
    Posts
    33
    Thanks again.

    I will try to get my own solution before you fall sleep.

    Quote Originally Posted by Pat Phelan
    I could talk for hours about GROUP BY, but it would bore you to tears... I'll let you chew on this one a bit (a couple of hours), and if you don't have a solution by then I'll offer one. I'm betting that you find your own solution long before then though, because you seem to have a good grasp of the fundamentals and just needed a nudge in the right direction.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Posts
    33
    Well, I don't think I can get it.

    Please help on the detail.


    Quote Originally Posted by leau
    Thanks again.

    I will try to get my own solution before you fall sleep.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try:
    Code:
    CREATE TABLE leau_a (
       Phone	VARCHAR(20)
    ,  Month	CHAR(5)
    ,  Cost		DECIMAL(5,2)
    ,  Company	VARCHAR(20)
       )
    
    CREATE TABLE leau_b (
       Phone	VARCHAR(20)
    ,  Month	CHAR(5)
    ,  Cost		DECIMAL(5,2)
    ,  Company	VARCHAR(20)
       )
    
    CREATE TABLE leau_c (
       Phone	VARCHAR(20)
    ,  Month	CHAR(5)
    ,  Cost		DECIMAL(5,2)
    ,  Company	VARCHAR(20)
       )
    
    INSERT INTO leau_a (
       Phone, Month, Cost, Company
    )  SELECT           '1231231233', '08/01', 4.95, 'AA'
       UNION ALL SELECT '1231231233', '07/01', 4.95, 'AA'
    
    INSERT INTO leau_b (
       Phone, Month, Cost, Company
    )  SELECT           '1231231233', '10/01', 12.87, 'AA'
       UNION ALL SELECT '1231231233', '09/01', 13.87, 'AA'
       UNION ALL SELECT '1231231233', '08/01', 15.87, 'AA'
       UNION ALL SELECT '1231231233', '07/01', 17.87, 'AA'
       UNION ALL SELECT '1231231233', '06/01', 11.87, 'AA'
    
    INSERT INTO leau_c (
       Phone, Month, Cost, Company
    )  SELECT           '1231231233', '10/01', 1.00, 'AA'
       UNION ALL SELECT '1231231233', '09/01', 1.5, 'AA'
       UNION ALL SELECT '1231231233', '08/01', 1.5, 'AA'
       UNION ALL SELECT '1231231233', '07/01', 1.0, 'AA'
    
    SELECT Coalesce(a.Phone, b.Phone, c.Phone) AS Phone
    ,  Coalesce(a.Month, b.Month, c.Month) AS Month
    ,  Sum(a.Cost) AS aCost
    ,  Sum(b.Cost) AS bCost
    ,  Sum(c.Cost) AS cCost
    ,  Coalesce(a.Company, b.Company, c.Company) AS Company
       FROM leau_a AS a
       FULL JOIN leau_b AS b
          ON (b.Phone = a.Phone
          AND b.Month = a.Month
          AND b.Company = a.Company)
       FULL JOIN leau_c AS c
          ON (c.Phone = Coalesce(a.Phone, b.Phone)
          AND c.Month = Coalesce(a.Month, b.Month)
          AND c.Company = Coalesce(a.Company, b.Company))
       GROUP BY Coalesce(a.Phone, b.Phone, c.Phone)
    ,     Coalesce(a.Month, b.Month, c.Month)
    ,     Coalesce(a.Company, b.Company, c.Company)
    -patP

  8. #8
    Join Date
    Feb 2004
    Posts
    33
    I finally follow your query and get my result. They are correct now.

    So in this way, I think I not only can solve this view, but also another view which is FULL JOIN 5 views.

    I really appreciate your great help.

Posting Permissions

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