[spacdude]

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.

[andrewst]

Your query will look something like this:

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

[spacdude]

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?

[andrewst]

Something like this, if I recall correctly:

strSQL = "select empno" _
             & " from emp" _
             & " where ename = ?"

i.e. the underscore is the "continued on next line" marker for VBScript.

[spacdude]

Thanks again for you help andrew

[spacdude]

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.

[andrewst]

Oh no, not the "One True Look-up Table"?

You can join to the same table twice like this:

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.

[r937]

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

[andrewst]

Who knows? Maybe you are right - it's equally possible!

[spacdude]

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.

[andrewst]

Well, I think I have already given you the SQL for that above.

[spacdude]

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!