Hi all,


Consider the following MySQL statement.


SELECT descriptions.descript_id,
descriptions.pack_id,
descriptions.cat_id,
descriptions.busname_display,
descriptions.public_address3,
descriptions.subcat_id,
descriptions.loc_id

FROM descriptions
WHERE descriptions.cat_id=#sp1_id# AND descriptions.subcat_id IN (#sp2_id#) AND descriptions.loc_id IN (#sp3_id#) LIKE '%#main_keyword#%';


================================================== =======

is there a problem with MySQL 4.1.15 where using the AND operator conflicts with the IN operator.

the variable "sp1_id" can only have a Numerical value from 1 to 18.

the variable "sp2_id" can have a STRING holding the following ... (1,2,3,4,5) cause of the commas.

The variable "sp3_id" can only have a STRING holding the following values from 1 to 6. ex: (1,2,3,4,5,6) - is a string because of the commas



But when the query is run, there is a problem. The query only shows the appropriate records found using the values specified ....sp1_id, sp2_id...


But sp3_id is not found in the query. It does not appear to be recognizing the last subquery.

This is not a multi-table query. It all occurs within one table called "descriptions".

Can someone tell me what is the issue with last subquery part "sp3_id"??

thank you

remove the LIKE part of the sentence

Hi thanx for that prompt reply.

One question tho, if I remove the LIKE "main_keyword", what can I replace it with to make sure the query is also pattern matching vs a keyword?

Thanx again...

which column?

Hi-

the columns specified in the select statement are the columns this query is being applied to...if that helps.

Would REGEXP work to replace the -LIKE "%#main_keyword#%"- part?

Basically this query *must* work with a text search also.

when you use the LIKE operator, you need to say "where X like Y"

your query was missing the X part, which is why i asked which column

you say "the columns specified in the select statement" but of the seven of them, i'm gonna go out on a limb and guess that all the columns with "id" in their names are numeric, and therefore not likely to be the subject of a LIKE query

furthermore, i'm also going to do some more thinking for you, and suggest that you probably don't want to require that the same keyword be present in both of the remaining columns, but rather, in at least one of them, which means that you require an OR construction

Looks like I got alot to learn about MySQL syntax!

Thanx a ton for that explanation! It makes sense to me now.


Cheers!