sql question

[Bob Bedford]

I've a query where I retrieve 2 codes.

select ida, idb from mytable.

I've an other table where I've some "labels".
id:1 text:shoes
id:2 text:socks

.....

Now, depending on what the values of the combination ida and idb are, I've
to show one of the field in table labels.

if(ida = 1 and idb = 1) idlabel = 1
if(ida = 1 and idb = 2) idlabel = 2
if(ida = 2 and idb = 1) idlabel = 3
.....
I don't know how to do so. Also, I've to be able to sort the result of the
query by the labelname.

It is possible ?

[NC]

Re: sql question

Bob Bedford wrote:[color=blue]
>
> I've a query where I retrieve 2 codes.
>
> select ida, idb from mytable.
>
> I've an other table where I've some "labels".
> id:1 text:shoes
> id:2 text:socks
>
> ....
>
> Now, depending on what the values of the combination ida and idb are,[/color]
[color=blue]
> I've to show one of the field in table labels.
>
> if(ida = 1 and idb = 1) idlabel = 1
> if(ida = 1 and idb = 2) idlabel = 2
> if(ida = 2 and idb = 1) idlabel = 3[/color]

Bad idea. You might want to combine the two tables into one:

=============== =============== ====
id ida idb text
=============== =============== ====
1 1 1 shoes
2 1 2 socks
3 2 1 whatever...
=============== =============== ====

Cheers,
NC

[Bob Bedford]

Re: sql question

> Bad idea. You might want to combine the two tables into one:[color=blue]
>
> =============== =============== ====
> id ida idb text
> =============== =============== ====
> 1 1 1 shoes
> 2 1 2 socks
> 3 2 1 whatever...
> =============== =============== ====[/color]
I can't. I have no control on the customer's table, and they can't be
changed.

[Colin McKinnon]

Re: sql question

Bob Bedford wrote:
[color=blue]
> I've a query where I retrieve 2 codes.
>
> select ida, idb from mytable.
>
> I've an other table where I've some "labels".
> id:1 text:shoes
> id:2 text:socks
>
> ....
>
> Now, depending on what the values of the combination ida and idb are, I've
> to show one of the field in table labels.
>
> if(ida = 1 and idb = 1) idlabel = 1
> if(ida = 1 and idb = 2) idlabel = 2
> if(ida = 2 and idb = 1) idlabel = 3
> ....
> I don't know how to do so. Also, I've to be able to sort the result of the
> query by the labelname.
>
> It is possible ?[/color]

erm yes.

Looking at what you've provided (I hope you don't really label your data
like that) you could use a really ugly hash function...

idlabel = idb + 2*(ida-1);

but I suspect that's not going to account for all cases - so create a look
up table, so:

SELECT a.ida, a.idb, b.idlabel
FROM mytable a, mylookup b
WHERE a.ida=b.ida AND a.idb=b.idb

HTH

C.

[Bob Bedford]

Re: sql question

> but I suspect that's not going to account for all cases - so create a look[color=blue]
> up table, so:
>
> SELECT a.ida, a.idb, b.idlabel
> FROM mytable a, mylookup b
> WHERE a.ida=b.ida AND a.idb=b.idb[/color]

Not that simple in fact...
so let's tell you what does work (after I've tried 2 hours, but too slow)

I've no control on table a and b and their values.
I've full control of the table labels, so it can be modified like I want it
or to better fit the query !
table LABELS struct:
labelcode tinyint
language char(4)
labeltext varchar(20)
*/
//I first store all labels text in an array
$labels = array();
if($RSlabels = mysql_query("se lect labeltext from labels where language =
'$lang' order by labelcode",$myd b))
while($RowLabel = mysql_fetch_obj ect($RSlabels))
array_push($lab els,$RowLabel->labeltext);
//labels array becomes here: shoes,socks,pan ts,shirts, t-shirts....
code1 can come from table a or table b
code2 can come from table a or table b

predefined possible values of code1 or code2 can be any alphabetic value.
Only some are interesting for the query.

now, the interesting part of my query is like this:
case (COALESCE(a.cod e1,b.code1)
when 'a' then '$labels[0]'
when 'b' then
if (COALESCE(a.cod e2,b.code2)<>'j ',
if(COALESCE(a.c ode2,b.code2)<> 'h','$labels[1]','$labels[3]'),'$labels[2]')
when 'c' then '$labels[4]'
when 'e' then '$labels[4]'
when 'd' then '$labels[5]'
when 'f' then '$labels[6]'
ELSE
case (COALESCE(a.cod e2,b.code2))
when 'j' then '$labels[2]'
when 'h' then '$labels[3]'
when 'g' then '$labels[4]'
when 'c' then '$labels[5]'
end
end as label,

This returns the desired label, but unfortunately, it's very slow (0.5secs)
and it's the most used query.
It's there any way to simplify the query or, better, change the labels table
structure to simplify the query?
like:
labels
id-code1-code2-notcode1-notcode2-lang-result
1 a en shoes
2 b j,h en socks
3 c en pants
4 d en shirts
5 e en t-shirts
6 f en shirts
7 g a,b,c,d,e,f en xxxxx
8 h a,b,c,d,e,f en yyyyy
9 c a,b,c,d,e,f en zzzzz
10 j a,b,c,d,e,f en wwww

but then how to build the query using code1 and code2 ??? for the same
result ?

Bob

[Steve]

Re: sql question

Bob Bedford wrote:[color=blue][color=green]
>> but I suspect that's not going to account for all cases - so create a
>> look
>> up table, so:
>>
>> SELECT a.ida, a.idb, b.idlabel
>> FROM mytable a, mylookup b
>> WHERE a.ida=b.ida AND a.idb=b.idb[/color]
>
>
> Not that simple in fact...
> so let's tell you what does work (after I've tried 2 hours, but too slow)
>
> I've no control on table a and b and their values.
> I've full control of the table labels, so it can be modified like I want
> it or to better fit the query !
> table LABELS struct:
> labelcode tinyint
> language char(4)
> labeltext varchar(20)
> */
> //I first store all labels text in an array
> $labels = array();
> if($RSlabels = mysql_query("se lect labeltext from labels where language
> = '$lang' order by labelcode",$myd b))
> while($RowLabel = mysql_fetch_obj ect($RSlabels))
> array_push($lab els,$RowLabel->labeltext);
> //labels array becomes here: shoes,socks,pan ts,shirts, t-shirts....
> code1 can come from table a or table b
> code2 can come from table a or table b
>
> predefined possible values of code1 or code2 can be any alphabetic value.
> Only some are interesting for the query.
>
> now, the interesting part of my query is like this:
> case (COALESCE(a.cod e1,b.code1)
> when 'a' then '$labels[0]'
> when 'b' then
> if (COALESCE(a.cod e2,b.code2)<>'j ',
>
> if(COALESCE(a.c ode2,b.code2)<> 'h','$labels[1]','$labels[3]'),'$labels[2]')
> when 'c' then '$labels[4]'
> when 'e' then '$labels[4]'
> when 'd' then '$labels[5]'
> when 'f' then '$labels[6]'
> ELSE
> case (COALESCE(a.cod e2,b.code2))
> when 'j' then '$labels[2]'
> when 'h' then '$labels[3]'
> when 'g' then '$labels[4]'
> when 'c' then '$labels[5]'
> end
> end as label,
>
> This returns the desired label, but unfortunately, it's very slow
> (0.5secs) and it's the most used query.
> It's there any way to simplify the query or, better, change the labels
> table structure to simplify the query?
> like:
> labels
> id-code1-code2-notcode1-notcode2-lang-result
> 1 a en shoes
> 2 b j,h en socks
> 3 c en pants
> 4 d en shirts
> 5 e en t-shirts
> 6 f en shirts
> 7 g a,b,c,d,e,f en xxxxx
> 8 h a,b,c,d,e,f en yyyyy
> 9 c a,b,c,d,e,f en zzzzz
> 10 j a,b,c,d,e,f en wwww
>
> but then how to build the query using code1 and code2 ??? for the same
> result ?
>
> Bob
>[/color]
Doesn't MySQL have views and unions? I'd have thought that either or
both would have worked. ISTR that views weren't available last time I
tried to use them, but I'm pretty sure an union does. Why not use that
to modify this data into a more useable format... maybe using subqueries
as well?

Steve