query on an array in Mysql

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

Threaded View
I've a table on mysql:

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

I do have some records:

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

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 !


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

Re: query on an array in Mysql

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

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.



Re: query on an array in Mysql

Quoted text here. Click to load it

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

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


Re: query on an array in Mysql

Bob Bedford wrote:
Quoted text here. Click to load it
If all you are after is the label change the * after SELECT to
DISTINCT a.label in order to eliminate the duplicates.


Re: query on an array in Mysql

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

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.


Re: query on an array in Mysql

Quoted text here. Click to load it

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

Re: query on an array in Mysql

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

You do indeed need MySQL 4.1 or higher for that.

Take a look here for how to rewrite that subquery:

Site Timeline