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.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|||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.
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|||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|||Thanks again.
I will try to get my own solution before you fall sleep.
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|||Well, I don't think I can get it.
Please help on the detail.
Thanks again.
I will try to get my own solution before you fall sleep.|||Try: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|||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.
Allen White wrote:
chemas-microsoft-com
ffice:excel">
ivotTable>
isplayScreenTips /> 
chemas-microsoft-com
ffice:excel">
ivotTable>
isplayScreenTips />