February 22, 2007 at 10:22 am
Proper use of joins?
Would joining a master table to a detail table, directly off one field and indirectly off a second field from a third table be proper? The master table contains a subset of all Code,subcode combinations of the detail. I create a cross-reference table listing the detail unique subcode and matching master subcode combinations.
Table Master ( Mcode , Msub, Mdesc, Mrate) PK Mcode,Msub
120, S111, White, 1.2
121, S111, black, 1.3
122, S111, red, 1.5
121, S222, white, 1.2
122, S222, blue, 5.5
Table Detail( ID, DX, Mcode , Dsub)
1, 1a, 120, S111
2, 1a, 121, S111A
3, 3d, 121, S22A
4, 1z, 122, S222S
Table Xref (Dsub, Msub) PK = Dsub (unique) ,Msub
S111 ,S111
S111a ,S111
S22A ,S222
S222S ,S222
Join Xref to Detail, then master to Detail thru Xref
Select * from detail
join Xref on Xref.DSub = Detail.DSub
join Master on Master.Mcode = Detail.MCode and Master.MSub = XREF.MSub
February 22, 2007 at 3:36 pm
I have to say you are very ambitious to use XRef to have cross-reference. It seems to me what you are trying is:
Select * from (Select detail.ID, detail.DX, detail.Mcode , detail.Dsub, Xref.Msub From detail
join Xref on Xref.DSub = Detail.DSub) As DetailRef
join Master on (Master.Mcode = DetailRef.MCode and Master.MSub = DetailRef.MSub)
February 22, 2007 at 4:55 pm
As a rule of thumb, when joining, the on criteria will be the primary key of one table equal to the foreign key of the other table.
If you are not joining on the primary key/foreign key, you will probably not get the expected results.
FYI: The DBMS SQLAnywhere has "KEY JOIN" as an join keywork but requires specification of the foreign key constraint name instead of column names. The syntax looks like:
FROM detail
key join xref by (constraint name)
key join Master by (constraint name)
SQL = Scarcely Qualifies as a Language
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply