looking for possible query

[asmith20002]

hi,

I have 2 tables.

The main is like

name img1 img2 img3
john 2 3 6
tom 5 6 2
jack 1 3 5
.
.

.

the second (img table)
id url
1 http...
2 http...
3 http...
.
.
.

I want to get all the data on the "main table".
and show like tabular data. but I need to get the image urls also.

If I use a simple join with 2 tables. it will give me more than 1 row for each user. for example the result will be like this :

name img1 img2 img3 id url
john 2 3 6 2 url(img2)
john 2 3 6 3 url(img3)
john 2 3 6 6 url(img6)
tom
.
.
.

I am wondering if there's any way to get them like this :
name img1 img2 img3 url1 url2 url3
john 2 3 6 url(img2) url(img3) url(img6)
tom 5 6 2 url(img5) url(img6) url(img2)
.
.

Is it possible?
Or any better way?

[dingbat]

I think you can do it with 3 joins, something like this:

select name, img1, img2, img3, u1.url, u2.url, u3.url
from imgTable
left join urlTable u1 on (imgTable.img1 = u1.id)
left join urlTable u2 on (imgTable.img2 = u2.id)
left join urlTable u3 on (imgTable.img3 = u3.id)

Or you can use subqueries, which is simpler in a large complex query, but may or may not be as efficient.