Using nested joins ?? (I think) :) 1

[shaunieb]

Hi, here is my query :

select pc1.colorid, pcol1 = color1, pcol2 = color2 from productcolors pc1 join colorcombo CB on (CB.id = pc1.colorid) where pc1.prodid = '8'

Here are my results :
colorid pcol1 pcol2
1 1 28
2 2 28
21 1 10
28 4 10
19 10 28
33 2 23
37 15 11
33 2 23

What I would like to do is use the table COLORCANVASS to reference colorname to pcol1 and pcol2 via id

id colorname
1 black
2 maroon
3 cherise
4 red
5 tomato red
6 blue
7 teal
8 brown
9 tan
10 white
11 yellow
12 orange
13 purple
14 b/green
15 jade
16 turquoise
17 royal blue
18 navy
19 light grey
20 beige
21 cream
22 bp green
23 sanlam
24 olive
25 apple green
26 caltex
27 light navy
28 nothing

any ideas?

Regards
Shaun

 

[gradley]

You can try using a sub-select statement for each color name, if you are looking to retrieve the color names instead of the column ID's:

Here is a sample:

select
pc1.colorid,
pcol1 = (select colorname from colorcanvass where [id] = color1),
pcol2 = (select colorname from colorcanvass where [id] = color2)
from productcolors pc1
join colorcombo CB
on (CB.id = pc1.colorid)
where pc1.prodid = '8'

 

[shaunieb]

Thank you very much gradley, you are a genius!

 

[ESquared]

I'm curious if this method is slower than regular joins in the from clause, for very large tables/result sets.

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.

 

[gradley]

I'm sure there are cases where using 2 INNER JOINS on the same table would be better than using the sub-select statements.

It really depends on the size of the tables (as you mentioned) and the indexing used.