sql question

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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 ?

Re: sql question

Bob Bedford wrote:
Quoted text here. Click to load it

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...


Re: sql question

Quoted text here. Click to load it

Re: sql question

Bob Bedford wrote:

Quoted text here. Click to load it

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



Re: sql question

Quoted text here. Click to load it

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("select labeltext from labels where language =
'$lang' order by labelcode",$mydb))
  while($RowLabel = mysql_fetch_object($RSlabels))
//labels array becomes here: shoes,socks,pants,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.code1,b.code1)
  when 'a' then '$labels[0]'
  when 'b' then
    if (COALESCE(a.code2,b.code2)<>'j',
  when 'c' then '$labels[4]'
  when 'e' then '$labels[4]'
  when 'd' then '$labels[5]'
  when 'f' then '$labels[6]'
  case (COALESCE(a.code2,b.code2))
    when 'j' then '$labels[2]'
    when 'h' then '$labels[3]'
    when 'g' then '$labels[4]'
    when 'c' then '$labels[5]'
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?
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 ?


Re: sql question

Bob Bedford wrote:
Quoted text here. Click to load it
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?


Site Timeline