#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    5
    Rep Power
    0

    HELP joining two tables


    Hello,

    I am creating a view that displays service codes and descriptions. I have two tables. Table 1 has service codes and table 2 has service code and description.

    all of the fields in table 1 are dependant on table 2.

    For example

    Table 1

    service_code1
    service_code2
    sevice_code3

    Table 2

    service_code
    service_code_description

    How can I join all three service codes from table 1 to table 2 to get each individual service code description?

    service_code1
    service_code_description1

    service_code2
    service_code_description2

    service_code3
    service_code_description3
  2. #2
  3. No Profile Picture
    Average Intelligence
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2003
    Location
    Ohio/Chicago
    Posts
    678
    Rep Power
    12
    how about you explain a little better and give us a few real values. Table one sounds completely erroneous if it's redundant in the primary key in table two.
    this is what i'm pictureing
    Code:
    table 1              table 2
    servcode          servcode         desc
    123                   123                  blah
    463                   463                  blah
    987                   967                  blah
    are certain descriptions missing where you have something in table 1 but nothing in table 2 and you want to know how to join these tables keeping the values in table 1 even though you have no entry in 2? Perhaps i just didn't understand your schema explanation....
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    New York
    Posts
    49
    Rep Power
    12
    Include the same description table three times. You can do this by using aliases, like this:

    SELECT
    a.Service_code1, b.Service_code_description,
    a.Service_code2, c.Service_code_description,
    a.Service_code3, d.Service_code_description
    FROM table1 a, table2 b, table2 c, table2 d
    WHERE
    a.Service_code1=b.Service_code
    AND a.Service_code2=c.Service_code
    AND a.Service_code3=d.Service_code

    -Dave
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    5
    Rep Power
    0
    hmm,

    table 1 contains three different service code fields.

    exterior_service_code1 <--- contains 100 distinct codes
    interior_service_code2 <--- contains 50 distinct codes
    misc_service_code3 <--- contains 25 distinct codes

    table 2 contains all of the descriptions for each code

    service_code <--------contains all codes (exterior_service_code1, interior_service_code2 and misc_service_code3) <--- contains 175 distinct codes

    serivice_description <-- contains 175 distinct descriptions

    so the three fields get their description from table 2

    does this help?
    Last edited by method; August 26th, 2003 at 03:52 PM.
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Posts
    5
    Rep Power
    0
    Originally posted by WineIsGood
    Include the same description table three times. You can do this by using aliases, like this:

    SELECT
    a.Service_code1, b.Service_code_description,
    a.Service_code2, c.Service_code_description,
    a.Service_code3, d.Service_code_description
    FROM table1 a, table2 b, table2 c, table2 d
    WHERE
    a.Service_code1=b.Service_code
    AND a.Service_code2=c.Service_code
    AND a.Service_code3=d.Service_code

    -Dave
    PERFECT! thanks Dave!

IMN logo majestic logo threadwatch logo seochat tools logo