Proper Use of Joins

  • 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

     

     

  • 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)

  • 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