Results 1 to 3 of 3

Thread: Join Tables

  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Join Tables

    I have a table name tech_id contains field CORP,Contractor. There is another table name LastValidTech contains four fields, Account1, Account2, Account3, Account4. Table structure is first 4 digits of LastValidTech (Account1,Account2,Account3,Account4) is CORP.

    tech_id
    CORP,CONTRACTOR
    1211,ABC
    1222,EFG
    1333,KKL

    LastValidTech
    TicketNo, Account1, Account2, Account3, Account4
    1122,01211-12345-01
    1123, ,01222-32456-93
    1124, 01333-73737-04

    Some times the Account1 has a value, Some times Account2 in another record has a value and same some times Account3 and Account4 has a value. How to i join both tables in a sense that if Account1 has a value and match with first 4 digits of CORP then it will connect to CORP or if Account2 has a value and match with first 4 digit with CORP it will connect to CORP or if Account3 and Account4 has a value and match with first 4 digit with CORP then it will connect to CORP.

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Your design sounds iffy to say the least. Are you familair with normalisation and relational database design?

    http://r937.com/relational.html
    http://www.datamodel.org/NormalizationRules.html

    Have a read of the above. A normalised design and a rethink of your join columns could make this a trivial query.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2006
    Posts
    265
    A more enlightened why of putting it is your second table LastValidTech should be structured with only one account field

    TicketNo, CORP, Account

    Separate the Account into CORP into in constituent parts

    If there were 4 (old) Accounts values relating to the Ticket there would be 4 records if there was one there would ne one record.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •