Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2004
    Location
    UK
    Posts
    15

    Unanswered: Combining info from two tables?

    Hello, I'm having some problems trying to access two tables in a SQL database at the same time and making some results out of them. Let me explain further: the first table has some information in that I'm going to be doing a select query on and reading out, but one of the columns in this table is a set of codes, the second table contains the codes in one column and their meanings in the other.

    So I want to bring back the information from the first table and then select the information for the codes shown from the second table and print their meanings alongside the information from the first table. Could anyone help me out in figuring out how my SQL in the ASP page for this would be written? Sorry if this is a little confusing but im having a hard time visualising how to do this.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Your query will look something like this:

    Code:
    select t1.col1, t1.col2, t1.col3, t1.code, t2.description
    from t1
    join t2 on t1.code = t2.code
    where ...;

  3. #3
    Join Date
    Sep 2004
    Location
    UK
    Posts
    15
    Cheers, it works. I've only been using very basic SQl views in the past so joins etc are new to me. I do have another question tho, I'm writing these queries in an ASP page and they are very long single lines at the mo, how do I break them up into seperate shorter lines?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Something like this, if I recall correctly:
    Code:
    strSQL = "select empno" _
                 & " from emp" _
                 & " where ename = ?"
    i.e. the underscore is the "continued on next line" marker for VBScript.

  5. #5
    Join Date
    Sep 2004
    Location
    UK
    Posts
    15
    Thanks again for you help andrew

  6. #6
    Join Date
    Sep 2004
    Location
    UK
    Posts
    15

    Question

    Ok i've hit another snag with this, there are two different columns in the first table that are codes that need to be compared to the code listing in the 2nd table and then their code meanings sent back. I ve done this with one which is where u're first piece of code comes in handy but i can't join two columns in the 1st table to the one in the 2nd.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oh no, not the "One True Look-up Table"?

    You can join to the same table twice like this:
    Code:
    select t1.col1, t1.col2, t1.col3, t1.code1,
           t2_1.description, t1.code2, t2_2.description
    from t1
    join t2 t2_1 on t1.code1 = t2_1.code
    join t2 t2_2 on t1.code2 = t2_2.code
    where ...;
    t2_1 and t2_2 are "aliases" for the table t2 which now appears twice in the query.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's funny, i took "one of the columns in this table is a set of codes" to mean something completely different (a denormalized table)

    looks like you may have understood the requirements better than i did, tony

    see http://forums.devshed.com/t193376/s.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Who knows? Maybe you are right - it's equally possible!

  10. #10
    Join Date
    Sep 2004
    Location
    UK
    Posts
    15
    Sorry guys I think i'm over complicating the matter, let me try to explain it in a minimal way,

    Table 1 with 3 columns, we'll call it date, the next column we will call code1 and the last column we will call code2.

    Table 2 has 2 columns, one called error_codes and another called translation with is the text meaning of the error codes.

    I want the query to select all the records in the first table for a certain criteria and give the translations for the code columns in the first table from the 2nd table.

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, I think I have already given you the SQL for that above.

  12. #12
    Join Date
    Sep 2004
    Location
    UK
    Posts
    15
    I've actually found that the 2nd column in the first table referred to a different set of codes in another table so the join will work now, thank you both for your help, i'm learning it bit by bit!

Posting Permissions

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