Need SQLHelp

[mark1110]

Hi,

I have 2 tables, in the first table (test1) I have ID1, ID2, ID3, ID4, and ID5. The second table (test2)I have ID and State. What I would like to do is to get the state associated with each ID for each field. For example, if ID1 = "IL", ID2 = "IN", ID3 = "CA", ID4 = "NV", and ID5 = "NY", I would like to get Illinois, Indiana, California, Nevada, and New York. The field ID in test2 has all the state abbreviations and the state field has the state spelled out, ID1 - ID5 has the state abbreviations or a blank. I have tried the following with no luck:

SELECT id as id_1, id as id_2, id as id_3, id as id_4, id as id_5, id1, id2, id3, id4, id5
FROM test1, test2
WHERE id_1 = id1 AND id_2 = id2 AND id_3 = id3 AND id_4 = id4 AND id_5 = id5

I am not sure that this is even the right approach. Has anyone done anything similar to this? If so, what did you do?

Thanks,

Mark

 

[mrdenny]

You need to join to the test2 table 5 times like this.

select s1.state, s2.state, s3.state, s4.state, s5.state
from test1
join test2 s1 on test1.id1 = s1.id
join test2 s2 on test1.id2 = s2.id
join test2 s3 on test1.id3 = s3.id
join test2 s4 on test1.id4 = s4.id
join test2 s5 on test1.id5 = s5.id

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[PHV]

I'd use LEFT OUTER JOIN ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mark1110]

Will this work in Access 97?

Thanks,

Mark

 

[PHV]

Access syntax:
SELECT A.ID1, S1.State, A.ID2, S2.State, A.ID3, S3.State, A.ID4, S4.State, A.ID5, S5.State
FROM ((((test1 AS A
LEFT JOIN test2 AS S1 ON A.ID1 = S1.ID)
LEFT JOIN test2 AS S2 ON A.ID2 = S2.ID)
LEFT JOIN test2 AS S3 ON A.ID3 = S3.ID)
LEFT JOIN test2 AS S4 ON A.ID4 = S4.ID)
LEFT JOIN test2 AS S5 ON A.ID5 = S5.ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886