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.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.

Asked a while back - think Ill try again!

This pertains to SQL Server 2000

Do others have the problems that we have in our company, where we have
a field from a table, and need to know all the SQL Server processes
that may change that value? I'm still hoping somebody can tell me of a
tool, or set of queries or something that will document the fields in
the tables and the processes that affect them, along with the values
that it sets. For example:

TableA.FieldA:
sp_InsertNewRec: sets field to Null when new record created
sp_ChangeStatus: sets field to the value of the parameter passed in
tr_LogChanges: sets field to be the current datetime

Maybe we're just too sloppy at my company, but it seems to be fairly
obvious need to have some sort of automated process that can provide
that documentation. With thousands of fields, hundreds of stored
procedures, triggers, DTS's etc..., documenting this information is a
VERY daunting task. Any help at all would be appreciated.

GaryOn 6 Nov 2003 15:19:44 -0800, garyderousse@.yahoo.com (Gary DeRousse)
wrote:
>Do others have the problems that we have in our company, where we have
>a field from a table, and need to know all the SQL Server processes
>that may change that value? I'm still hoping somebody can tell me of a
>tool, or set of queries or something that will document the fields in
>the tables and the processes that affect them, along with the values
>that it sets. For example:

It would perhaps be possible, though not trivial, to construct a
report that extracts what fields and what tables are affected by
procs, triggers, functions, etc. by parsing the entire database's SQL
(which you can create in Enterprise Manager).

It would be very difficult to describe the functionality that said
proc does on said table, since the SQL language is so dynamic. Such
basic technical documentation should have been done by the coder when
the procedure was created, if this level of thoroughness was desired.
You'll find out that reverse engineering a SQL server application is
fairly time consuming. :)
___________
To replay by email, chop off the head!