Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    10

    Unhappy Unanswered: multiple AND with IN specs - problem syntax?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    remove the LIKE part of the sentence
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Posts
    10

    Is there an alternative?

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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2004
    Posts
    10
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    Code:
    WHERE descriptions.cat_id = #sp1_id# 
      AND descriptions.subcat_id IN (#sp2_id#) 
      AND descriptions.loc_id IN (#sp3_id#) 
      AND (
          descriptions.busname_display LIKE '%#main_keyword#%'
       OR descriptions.public_address3 LIKE '%#main_keyword#%'
          )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2004
    Posts
    10
    Looks like I got alot to learn about MySQL syntax!

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


    Cheers!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •