skennedy@ihlpb.ATT.COM (S. Kennedy) (12/11/88)
Hello!
I got a problem and just cannot figure out how to do it.
I am trying to create a select that will do a one to many to one to one
relationship.
SCENARIO
========
I have four tables that I need to group.
Table A Table B Table C Table D
------- ------- ------- -------
leadsource leadsource leadsource
location location location
type type
leadnumber
rundate
cost
description
description
Tables A, C and D will have a one to one match as Tables C & D are simply
description tables for codes in Table A.
Leadsource, location, and type make up a unique filed. There are many locations
for the same leadsource and several types for one location.a (Maybe too
relational??) The real kicker comes when Table B may have several entrys for
the same leadsource, location and type but with unique rundates.
The following:
select
A.*, B.*, C.*
from C, outer (A, outer B)
where C.leadsource = A.leadsource
and C.leadsource = B.leadsource
and A.leadsource = B.leadsource
and A.location = B.location
and A.type = B.type
into temp temp01;
select
temp01.*, D.*
from temp01, outer D
where A.location = D.location
order by C.leadsource, D.location, A.type, A.rundate, A.leadnumber
yeilds me a select that only gets me an entry if there is a matching one in
A and if there are two B entries (different rundate), it gets the same A pair
for each B.
Value 1 Value 2 Value 3 Value 4
------- ------- ------- -------
A.leadsource aaa aaa zzzz
B.leadsource aaa aaa
C.leadsource aaa aaa zzzz ttt
A.type s s r
B.type s s
B.location a1 a1
A.location a1 a1 a1
D.location a1 a1 a1
D.description (desc) (desc) (desc)
C.description (desc) (desc) (desc) (desc)
B.rundate 01/01/88 01/01/87
B.cost 1000.00 300.00
A.leadnumber 100 100
Value 1 & 2
-----------
For the same leadsource (aaa), location (a1), type (s) and leadnumber 100,
there are two different costs (1000, 300). Therefore, I received an entry
twice for the same lead. (Twice as many forms selected.)
Value 3
-------
No cost assocaited with that leadsource. No problem.
Value 4
-------
No leadsource in table A to associate with table B or C. Looses the form from
table B.
SOLUTION??
========
To be able to only get :
1) as many forms as it needs to do this.
2) a form from table B when there is no matching form from table A.
3) Only as many duplicates as necessary and a way to process and dispose
of the duplicates.
How?? I just can't figure out how to arrange the select statement to fix
this.
Sorry this is so long. Please e-mail any possible solutions to
att!ihlpb!skennedy
Shawn Kennedy
AT&T Bell Labs
Thanks to all that repliy in advancve,
Shawn Kennedy AT&T Bell Labs
att!ihlpb!skennedy skennedy@ihlpb!att.com
1 312 979 2494
donovan@hpsmtc1.HP.COM (Donovan Hsieh) (12/13/88)
It appears that your DB schema is not NORMALIZED into proper normal form (e.g. 3 NF). For example in Table A, I can formulate the following set of Functional Dependencies (FD) based on your description on the relationship of each attribute for each FD : FD 1 : leadsource location type --> leadnumber /* unique key on the LHS */ FD 2 : location --> leadsource /* many location for the same leadsource, I also assume that each location only has one leadsource */ FD 3 : type --> location /* several types for one location, and I assume that each type only has one location */ The above DB scheme only satisfies 2nd normal form, i.e. there exists Transitive Dependancy from FD 2 to FD 1 and FD 3 to FD 2. Further decompostion into 3rd normal form is required to achieve lossless join property , i.e. the join anomaliy sympton which you have described. And In your table B description "Table B may have several entries for the same leadsource, location and type but with unique rundate", it doesn't make any sense to me. Donovan Hsieh Hewlett-Packard Software Method Lab email : donovan@hpsmtc1.HP.COM
skennedy@ihlpb.ATT.COM (S. Kennedy) (12/15/88)
In article <11410003@hpsmtc1.HP.COM> donovan@hpsmtc1.HP.COM (Donovan Hsieh) writes: >It appears that your DB schema is not NORMALIZED into proper normal form (e.g. >3 NF). For example in Table A, I can formulate the following set of Functional >Dependencies (FD) based on your description on the relationship of each >attribute for each FD : > > FD 1 : leadsource location type --> leadnumber /* unique key on the LHS */ > FD 2 : location --> leadsource /* many location for the same leadsource, > I also assume that each location only > has one leadsource */ > FD 3 : type --> location /* several types for one location, and I assume > that each type only has one location */ > >The above DB scheme only satisfies 2nd normal form, i.e. there exists >Transitive Dependancy from FD 2 to FD 1 and FD 3 to FD 2. Further decompostion >into 3rd normal form is required to achieve lossless join property , i.e. the >join anomaliy sympton which you have described. > >And In your table B description "Table B may have several entries for the same >leadsource, location and type but with unique rundate", it doesn't make any >sense to me. Let me show an example of this possibly screwed up structure. TABLE B Leadssource Location Type Rundate Cost ----------- -------- ---- ------- ---- YRHD B1 R 06/01/88 300.00 YRHD B1 S 06/01/88 300.00 YRHD B1 B 06/01/88 300.00 (through 24 more location sets) YRHD B1 R 06/01/87 500.00 YRHD B1 S 06/01/87 700.00 YRHD B1 B 06/01/87 200.00 (through 24 more location sets) CBW A0 R 05/01/88 1100.00 CBW A0 S 05/01/88 200.00 INWJ A0 R 09/12/88 50.00 INWJ A0 B 09/12/88 50.00 (etc) TABLE A Leadssource Location Type leadnumber ----------- -------- ---- ---------- YRHD B1 R 1347 YRHD B1 R 1653 YRHD B1 B 987 CBW A0 R 466 CBW A0 S 1433 CBW A0 S 1944 USER A0 S 616 USER A0 S 789 USER A0 S 987 (etc) So I would have an entry in Table A that would not match Table B because there was no cost associated with that leadsource/location/type )LLT). Code USER for example. Rundate is unique for each occurence of LLT. Table C contains the list of *all possible leadsources* so I used that as my driving table to sort by leadsource. Would I have to come up with a unique Master Table to get around all of this?? Shawn Kennedy att!ihlpb!skennedy AT&T Bell Labs
donovan@hpsmtc1.HP.COM (Donovan Hsieh) (12/16/88)
According to your base note which states "Leadsource, location, and type make up a unique field". Your sample table A in response 2 has just invalidated this assumption : TABLE A Leadssource Location Type leadnumber ----------- -------- ---- ---------- YRHD B1 R 1347 (1) YRHD B1 R 1653 (2) YRHD B1 B 987 (3) CBW A0 R 466 (4) CBW A0 S 1433 (5) CBW A0 S 1944 (6) USER A0 S 616 (7) USER A0 S 789 (8) USER A0 S 987 (9) (etc) The tuples (1) & (2) show that "Leadsource Location Type" does not uniquely identify the "leadnumber". This invalidates my initial assumption of FD 1 listed in response 1 and your statement as described above. Further more, tuples (4) to (9) also invalidate the FD 2 since "Location" does not uniquely identify the "Leadsource". At this point, I would suggest you to either use a Entity-Relationship diagram or formal Funtional Dependency to specify your attribute relationship before I could make meaningful recommendation.
skennedy@ihlpb.ATT.COM (S. Kennedy) (12/20/88)
In article <11410004@hpsmtc1.HP.COM> donovan@hpsmtc1.HP.COM (Donovan Hsieh) writes: >According to your base note which states "Leadsource, location, and type make >up a unique field". Your sample table A in response 2 has just invalidated >this assumption : > >TABLE A >Leadssource Location Type leadnumber >----------- -------- ---- ---------- > > YRHD B1 R 1347 (1) > YRHD B1 R 1653 (2) > YRHD B1 B 987 (3) > CBW A0 R 466 (4) > CBW A0 S 1433 (5) > CBW A0 S 1944 (6) > USER A0 S 616 (7) > USER A0 S 789 (8) > USER A0 S 987 (9) > (etc) > > >The tuples (1) & (2) show that "Leadsource Location Type" does not uniquely >identify the "leadnumber". This invalidates my initial assumption of FD 1 listed >in response 1 and your statement as described above. Further more, tuples (4) >to (9) also invalidate the FD 2 since "Location" does not uniquely identify >the "Leadsource". > But that's my point. In table A, each lead must have a leadsource location type (LLT), but you may have several leads with the same LLT (You would hope so. If not, you did a pretty poor job in choosing your advertising! :-) and you would want to be able to take a count of them and their earned revenue based on their LLT. That is why the order by at the end is ledasource, location, type, rundate, leadnumber. In table B, you would have all the costs of the advertisement LLTs. You may have the LLT in twice, but the *rundate* would be different. What I have been told by several people is that I must come up with a master table by either unioning table A and table B together and choosing as outers all of the tables to this master table; or to create one dynamically and use that as my lookup table. (This would be faster as temp tables have no indexes.) shawn ---- Shawn Kennedy att!ihlpb!skennedy AT&T Bell Labs +1 312 979 2494