removing duplicate colum records, hmm 1

[shaunieb]

Hi everyone,

What Im trying to do is not display duplicate color records in the pcol1 column, heres my sql string and the results are shown below that. The pcol1 column contains the 2 b/green 3 red and 2 navy records.

Thanks In advance
Shaun

select pc1.id, pc1.colorid, pcolid = (select [id] from colorcanvass where [id] = color1), pcolpic1 = (select colorpic from colorcanvass where [id] = color1), 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 = 102


134 4 14 IMAGES/pallete/canvas A/h15_bgreen.gif b/green nothing
135 22 14 IMAGES/pallete/canvas A/h15_bgreen.gif b/green white
136 3 4 IMAGES/pallete/canvas A/h4_red.gif red nothing
137 26 4 IMAGES/pallete/canvas A/h4_red.gif red white
138 35 4 IMAGES/pallete/canvas A/h4_red.gif red yellow
139 25 18 IMAGES/pallete/canvas A/h19_navy.gif navy white
140 34 18 IMAGES/pallete/canvas A/h19_navy.gif navy l/grey

 

[vongrunt]

When it comes to duplicates, first question to answer is always: which record do you want to keep? The oldest in group perhaps?

 

[shaunieb]

Hi vongrunt,

It doesnt matter in this case but if it did how would i do it?

Thanks
Shaun

 

[shaunieb]

1 more thing. How do I run a query off the results above?

Thanks
Shaun

 

[vongrunt]

If it doesn't matter, then other columns should not be SELECTed. Take the first two records:

id  colorid pcolid pcolpic1                               pcol1    pcol2  
134       4     14 IMAGES/pallete/canvas A/h15_bgreen.gif b/green  nothing
135      22     14 IMAGES/pallete/canvas A/h15_bgreen.gif b/green  white

Columns colorid and pcol2 aren't duplicated. So if you pick first record (id=134) for example, results may be (and will be) interpreted by human as there is only one record with colorid=4 and pcol2=nothing... which is logically wrong.

Anyway, if other columns are kind of irrelevant try this. True "duplicate" is column pc1.color1; columns pcolid, pcolpic1 and pcol1 are joined. First isolate all distinct values of color1, plus smallest primary key value (I assumed this is column id):

select color1, min(id) as id from productcolors where prodid=102 group by color1

Then use original table and join it with results from above. This basically returns first record for each set of "duplicates":

select pc1.*
from productcolors pc1
inner join
( select color1, min(id) as id from productcolors where prodid=102 group by color1 ) X
on pc1.id=X.id

After that use pc1 to join with other tables as usual. Btw. you can reduce number of subqueries as well.

 

[shaunieb]

Thanks for the help

The reason it doesnt matter is because I want to display the avaliable single colors of a product ie pcol1 first and then after a user clicks on that particular color image it will then display the variant colors avaliable pcol2 for pcol1.

Does that make sense?

Regards
Shaun

 

[vongrunt]

Makes sense... then maybe is possible to use two separated queries:

- one to get available colors (pcol1) and nothing else
- another to get variants for specified pcol1

Of course it would be the best to have extra table for color variants.

 

[shaunieb]

I do have a table for color variants. Heres all the tables in question.

colorcombo
ID color1 color2
1 1 28
2 2 28
3 4 28
4 14 28
5 17 28
6 18 28
7 20 28
8 22 28
9 16 28
10 9 28
12 3 28
13 27 28
14 23 28
15 11 28
16 11 28
17 6 28
18 13 28
19 10 28
20 8 28
21 1 10
22 14 10
23 1 11
24 2 10
25 18 10
26 4 10
27 17 10
29 6 10
30 1 19
31 15 10
32 27 10
33 2 23
34 18 19
35 4 11
36 12 22
37 15 11
38 22 11
39 11 10

productcolors
id prodid colorid
63 90 4
64 90 22
65 90 1
66 71 32
67 0 32
68 53 22
69 0 22
70 54 1
71 54 23
72 47 21
73 88 7
74 88 7
75 91 22
76 91 22
77 91 17
78 91 37
79 92 22
80 92 1
81 92 20
82 93 4
83 93 7
84 93 17
85 94 4
86 94 7
87 95 22
88 96 4
89 96 7
90 96 29
91 96 31
95 97 32
96 97 33
97 97 6
98 97 5
99 97 15
104 24 1
105 24 1
106 24 1
103 24 4
107 24 1
108 98 4
111 99 1
110 98 21
112 99 2
113 99 3
114 99 6
115 99 7
116 99 9
117 99 10
118 99 15
119 99 12
120 99 13
121 100 1
122 100 3
123 100 4
124 100 5
125 100 6
126 100 7
127 101 4
128 101 1
129 101 2
130 101 3
131 101 6
132 101 7
133 101 5
134 102 4
135 102 22
136 102 3
137 102 26
138 102 35
139 102 25
140 102 34

colorcanvass
id colorname colorpic r g b
1 black IMAGES/pallete/canvas A/h1_black.gif 45 37 51
2 maroon IMAGES/pallete/canvas A/h2_maroon.gif 155 37 98
3 cherise IMAGES/pallete/canvas A/h3_cherise.gif 244 76 131
4 red IMAGES/pallete/canvas A/h4_red.gif 207 28 45
5 tomato red IMAGES/pallete/canvas A/H5_tom-red.gif 198 44 65
6 blue IMAGES/pallete/canvas A/H6_blue.gif 66 119 174
7 teal IMAGES/pallete/canvas A/H7_teal.gif 85 174 160
8 brown IMAGES/pallete/canvas A/H8_brown.gif 93 62 55
9 tan IMAGES/pallete/canvas A/H9_tan.gif 202 148 85
10 white IMAGES/pallete/canvas A/H11_white.gif 248 244 249
11 yellow IMAGES/pallete/canvas A/H12_yellow.gif 235 196 89
12 orange IMAGES/pallete/canvas A/H13_orange.gif 255 129 52
13 purple IMAGES/pallete/canvas A/H14_Purple.gif 103 48 106
14 b/green IMAGES/pallete/canvas A/h15_bgreen.gif 63 116 102
15 jade IMAGES/pallete/canvas A/h16_jade.gif 46 164 127
16 turquoise IMAGES/pallete/canvas A/h17_turq.gif 97 193 219
17 r/blue IMAGES/pallete/canvas A/h18_royal.gif 61 105 180
18 navy IMAGES/pallete/canvas A/h19_navy.gif 71 72 122
19 l/grey IMAGES/pallete/canvas A/h20_ltgrey.gif 186 187 207
20 beige IMAGES/pallete/canvas A/h23_beige.gif 216 199 195
21 cream IMAGES/pallete/canvas A/h27_cream.gif 234 228 214
22 bp green IMAGES/pallete/canvas A/h40_bpgreen.gif 62 134 104
23 sanlam IMAGES/pallete/canvas A/h45_sanlam.gif 92 105 140
24 olive IMAGES/pallete/canvas A/h53_olive.gif 138 147 127
25 apple green IMAGES/pallete/canvas A/h65_applegreen.gif 148 190 101
26 caltex IMAGES/pallete/canvas A/h77_caltex.gif 75 111 120
27 l/navy IMAGES/pallete/canvas A/n37_ltnavy.gif 84 114 154
28 nothing none 0 0 0

 

[vongrunt]

Maybe I missed something but it seems that problem w/ duplicates doesn't exist in database at all. You joined table productcolors with many-to-many table (colorcombo) and "duplicates" appeared. Try this to get all colors for specified prodid:

select pc1.colorid, cc.colorname, cc.colorpic
from productcolors pc1
join colorcanvass cc on pc1.colorid=cc.id
where pc1.prodid = 102

... and this to get color variants for selected color:

select cc1.colorname, cc2.colorname
from colorcombo cc
join colorcanvass cc1 on cc.color1= cc1.id
join colorcanvass cc2 on cc.color2= cc2.id
where cc.id = <selected colorid>