query on an array in Mysql

[Bob Bedford]

I've a table on mysql:

code1 tinyint, code2 tinyint, notcode1 varchar(20),notcode2
varchar(20),label

I do have some records:
values(1,null,null,null,'text1');
values(2,null,null,'17,18','text2');
values(2,null,null,'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]

Bob Bedford wrote:

I've a table on mysql:

code1 tinyint, code2 tinyint, notcode1 varchar(20),notcode2
varchar(20),label

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

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]

"Stijn Verholen" <st***@skynet.be> a écrit dans le message de news:
42*********************@news.skynet.be...

Bob Bedford wrote:

I've a table on mysql:

code1 tinyint, code2 tinyint, notcode1 varchar(20),notcode2
varchar(20),label

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

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.

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,notcode1,notcode2,label)
insert into label values(1,null,null,null,'text1');
insert into label values(2,null,null,1,'text2');
insert into label values(null,18,null,null,'text3');
insert into label values(null,17,null,null,'text4');
insert into label values(3,null,null,null,'text5');
insert into label values(4,null,null,null,'text6');
insert into label values(5,null,null,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]

Bob Bedford wrote:

"Stijn Verholen" <st***@skynet.be> a écrit dans le message de news:
42*********************@news.skynet.be...

Bob Bedford wrote:

I've a table on mysql:

code1 tinyint, code2 tinyint, notcode1 varchar(20),notcode2
varchar(20),label

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

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.

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,notcode1,notcode2,label)
insert into label values(1,null,null,null,'text1');
insert into label values(2,null,null,1,'text2');
insert into label values(null,18,null,null,'text3');
insert into label values(null,17,null,null,'text4');
insert into label values(3,null,null,null,'text5');
insert into label values(4,null,null,null,'text6');
insert into label values(5,null,null,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

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]

Bob Bedford wrote:

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

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]

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

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]

Bob Bedford wrote:

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.

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

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