query on an array in Mysql

[Bob Bedford]

I've a table on mysql:

code1 tinyint, code2 tinyint, notcode1 varchar(20),not code2
varchar(20),lab el

I do have some records:
values(1,null,n ull,null,'text1 ');
values(2,null,n ull,'17,18','te xt2');
values(2,null,n ull,'15','text3 ');

look at the second record
in my case if code1 = 2 and (code2 <> 17 and code2 <> 18) it should return
'text2'.

But how to create a query since I've values for code1 and code2 ?

where $valcode1 = code1 and ($valcode2 not in notcode2) ?
but how to do not in notcode2 in mysql ?
I've full control on the table, so I may put any kind of field type (didnt'
find any array type in mysql and since enum and set are predefined values,
they seems not fit my need since the values are different from case to case.
but I may be wrong since I don't know how to use enum and set fields)

Of course, if the values are 2 and 17, text3 must be in the query result !

Bob

PS: I've no access to mysql NG with my ISP, so please don't tell me not to
ask in this NG.

[Stijn Verholen]

Re: query on an array in Mysql

Bob Bedford wrote:[color=blue]
> I've a table on mysql:
>
> code1 tinyint, code2 tinyint, notcode1 varchar(20),not code2
> varchar(20),lab el
>
> I do have some records:
> values(1,null,n ull,null,'text1 ');
> values(2,null,n ull,'17,18','te xt2');
> values(2,null,n ull,'15','text3 ');[/color]

Doesn't seem like a sound table structure, and i don't see a unique index.

I would alter your table to add an index and forget about the notcode1
column, then create a second table with an index, and columns for the
index of your first table and a possible value.

That way, u can select the id from the first table, and use that to get
at the possible values for notcode1 in the second table.


HTH

Stijn

[Bob Bedford]

Re: query on an array in Mysql


"Stijn Verholen" <stijn@skynet.b e> a écrit dans le message de news:
421097c1$0$308$ ba620e4c@news.s kynet.be...[color=blue]
> Bob Bedford wrote:[color=green]
>> I've a table on mysql:
>>
>> code1 tinyint, code2 tinyint, notcode1 varchar(20),not code2
>> varchar(20),lab el
>>
>> I do have some records:
>> values(1,null,n ull,null,'text1 ');
>> values(2,null,n ull,'17,18','te xt2');
>> values(2,null,n ull,'15','text3 ');[/color]
>
> Doesn't seem like a sound table structure, and i don't see a unique index.
>
> I would alter your table to add an index and forget about the notcode1
> column, then create a second table with an index, and columns for the
> index of your first table and a possible value.
>
> That way, u can select the id from the first table, and use that to get at
> the possible values for notcode1 in the second table.[/color]

Thanks for your advice.
Actually this i a part of a very big query. but it takes too much time as I
do "case" and "if" in the query.
I must keep the notcode1, as the same code can be on the notcode2 and I've
to differenciate the 2 fields.
I've created the notcode table (id,code) so here are my records:
(code1,code2,no tcode1,notcode2 ,label)
insert into label values(1,null,n ull,null,'text1 ');
insert into label values(2,null,n ull,1,'text2');
insert into label values(null,18, null,null,'text 3');
insert into label values(null,17, null,null,'text 4');
insert into label values(3,null,n ull,null,'text5 ');
insert into label values(4,null,n ull,null,'text6 ');
insert into label values(5,null,n ull,null,'text5 '); //yes also text5

notcode
(id,code)
insert into notcode values(1,17);
insert into notcode values(1,18);

my test values (table a).
code1,code2 (for simulating what comes from a very big query)
insert into a values(1,0); //should return text1
insert into a values(2,9); //should return text2
insert into a values(3,8); //should return text5
insert into a values(7,9); //should return nothing
insert into a values(10,17); //should return text4

Anyway ,what would be the query ?

I've tried this
SELECT *
FROM a,
label left join notcode on label.notcode2 = notcode.id
WHERE a.code1 = label.code1
OR a.code2 = label.code2
AND (label.notcode2 is null or label.code2 <> notcode.code)

this gives me:
text1
text 2
text 2 //again, but looking at the result, it returns one for 17 and one for
18 as it's linked to notcode
text 5
text 4

This seems to work, but how to avoid the 2 times text 2 (it should return
only 1 result)? I absolutely need only 1 result.

Bob

[Jerry Gitomer]

Re: query on an array in Mysql

Bob Bedford wrote:[color=blue]
>
> "Stijn Verholen" <stijn@skynet.b e> a écrit dans le message de news:
> 421097c1$0$308$ ba620e4c@news.s kynet.be...
>[color=green]
>> Bob Bedford wrote:
>>[color=darkred]
>>> I've a table on mysql:
>>>
>>> code1 tinyint, code2 tinyint, notcode1 varchar(20),not code2
>>> varchar(20),lab el
>>>
>>> I do have some records:
>>> values(1,null,n ull,null,'text1 ');
>>> values(2,null,n ull,'17,18','te xt2');
>>> values(2,null,n ull,'15','text3 ');[/color]
>>
>>
>> Doesn't seem like a sound table structure, and i don't see a unique
>> index.
>>
>> I would alter your table to add an index and forget about the notcode1
>> column, then create a second table with an index, and columns for the
>> index of your first table and a possible value.
>>
>> That way, u can select the id from the first table, and use that to
>> get at the possible values for notcode1 in the second table.[/color]
>
>
> Thanks for your advice.
> Actually this i a part of a very big query. but it takes too much time
> as I do "case" and "if" in the query.
> I must keep the notcode1, as the same code can be on the notcode2 and
> I've to differenciate the 2 fields.
> I've created the notcode table (id,code) so here are my records:
> (code1,code2,no tcode1,notcode2 ,label)
> insert into label values(1,null,n ull,null,'text1 ');
> insert into label values(2,null,n ull,1,'text2');
> insert into label values(null,18, null,null,'text 3');
> insert into label values(null,17, null,null,'text 4');
> insert into label values(3,null,n ull,null,'text5 ');
> insert into label values(4,null,n ull,null,'text6 ');
> insert into label values(5,null,n ull,null,'text5 '); //yes also text5
>
> notcode
> (id,code)
> insert into notcode values(1,17);
> insert into notcode values(1,18);
>
> my test values (table a).
> code1,code2 (for simulating what comes from a very big query)
> insert into a values(1,0); //should return text1
> insert into a values(2,9); //should return text2
> insert into a values(3,8); //should return text5
> insert into a values(7,9); //should return nothing
> insert into a values(10,17); //should return text4
>
> Anyway ,what would be the query ?
>
> I've tried this
> SELECT *
> FROM a,
> label left join notcode on label.notcode2 = notcode.id
> WHERE a.code1 = label.code1
> OR a.code2 = label.code2
> AND (label.notcode2 is null or label.code2 <> notcode.code)
>
> this gives me:
> text1
> text 2
> text 2 //again, but looking at the result, it returns one for 17 and one
> for 18 as it's linked to notcode
> text 5
> text 4
>
> This seems to work, but how to avoid the 2 times text 2 (it should
> return only 1 result)? I absolutely need only 1 result.
>
> Bob
>[/color]
If all you are after is the label change the * after SELECT to
DISTINCT a.label in order to eliminate the duplicates.

HTH
Jerry

[Stijn Verholen]

Re: query on an array in Mysql

Bob Bedford wrote:[color=blue]
> I've tried this
> SELECT *
> FROM a,
> label left join notcode on label.notcode2 = notcode.id
> WHERE a.code1 = label.code1
> OR a.code2 = label.code2
> AND (label.notcode2 is null or label.code2 <> notcode.code)
>
> [snip]
>
> This seems to work, but how to avoid the 2 times text 2 (it should
> return only 1 result)? I absolutely need only 1 result.
>
> Bob
>[/color]

I still isn't quite clear to me how your database is structured and even
what it is exactly u are trying to accomplish. I have a gut feeling
there are better ways to do what u are doing.

That said, your query needs a subquery. Try this:

SELECT * FROM a, label, notcode
WHERE (a.code1 = label.code1 OR a.code2 = label.code2) AND
(label.notcode2 IS NULL OR
label.code2 NOT IN (SELECT code FROM notcode WHERE id = label.notcode2) );

I have not tested this btw.


Stijn

[Bob Bedford]

Re: query on an array in Mysql

> That said, your query needs a subquery. Try this:[color=blue]
>
> SELECT * FROM a, label, notcode
> WHERE (a.code1 = label.code1 OR a.code2 = label.code2) AND
> (label.notcode2 IS NULL OR
> label.code2 NOT IN (SELECT code FROM notcode WHERE id = label.notcode2) );
>
> I have not tested this btw.[/color]

I'ts what I'm trying to do since the beginning, but the query fails at mysql
4 doesn't seem to allow sub-queries

[Stijn Verholen]

Re: query on an array in Mysql

Bob Bedford wrote:[color=blue][color=green]
>> That said, your query needs a subquery. Try this:
>>
>> SELECT * FROM a, label, notcode
>> WHERE (a.code1 = label.code1 OR a.code2 = label.code2) AND
>> (label.notcode2 IS NULL OR
>> label.code2 NOT IN (SELECT code FROM notcode WHERE id = label.notcode2) );
>>
>> I have not tested this btw.[/color]
>
>
> I'ts what I'm trying to do since the beginning, but the query fails at
> mysql 4 doesn't seem to allow sub-queries
>[/color]

You do indeed need MySQL 4.1 or higher for that.

Take a look here for how to rewrite that subquery:
http://dev.mysql.com/doc/mysql/en/re...ubqueries.html