asking help on Full Outer Join on multi-tables

leau · #1 (**permalink**) 11-05-04, 10:46

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.

Pat Phelan · #2 (**permalink**) 11-05-04, 11:05

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

leau · #3 (**permalink**) 11-05-04, 11:10

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

Pat Phelan · #4 (**permalink**) 11-05-04, 11:13

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

leau · #5 (**permalink**) 11-05-04, 11:24

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

leau · #6 (**permalink**) 11-05-04, 13:26

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.

Pat Phelan · #7 (**permalink**) 11-05-04, 13:30

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

leau · #8 (**permalink**) 11-05-04, 13:53

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech