Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: MySQL IN clause help

    Hi

    I have the following stored procedure:

    Code:
    CREATE PROCEDURE get_customers(IN inCustomerIdList VARCHAR(100))
    BEGIN
    
      SELECT first_name, last_name, email FROM tbl_customers  
      WHERE customer_id IN (inCustomerIdList);
    
    END$$
    The above should retrieve all customers whose id appears in the inCustomerIdList variable. inCustomerIdList is a string variable e.g. "8,9,5,6,10".

    However the above procedure only retreives the customer which has the id number of the first id in the above string. Now I know i would have to put each id in quotes to retrieve all customers in this list i.e ('8','9','5','6','10').

    Is there any way for me to convert the original string variable so that i can pass it in just like this: (8,9,5,6,10) or is there a MySQL function to concat apostraphes to each id?

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I suppose it's not worth me telling you why your general design is poor but to get you out of a fix you could try something like :
    Code:
    CREATE PROCEDURE get_customers(IN inCustomerIdList VARCHAR(100))
    BEGIN
    
      SELECT first_name, last_name, email FROM tbl_customers  
      WHERE concat( ',',inCustomerIdList,',' ) like concat( '%,',customer_id,',%' );
    
    END$$

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by mike_bike_kite
    I suppose it's not worth me telling you why your general design is poor but to get you out of a fix you could try something like :
    Code:
    CREATE PROCEDURE get_customers(IN inCustomerIdList VARCHAR(100))
    BEGIN
    
      SELECT first_name, last_name, email FROM tbl_customers  
      WHERE concat( ',',inCustomerIdList,',' ) like concat( '%,',customer_id,',%' );
    
    END$$

    Thanks for the tip but i've managed to find an even easier solution by using the FIND_IN_SET clause. By the way why is the design poor?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    because FIND_IN_SET, and any other solution such as the more complex LIKE one, do not scale

    unable to utilize an index, they get slower and slower as the number of rows increases

    plus, updates and deletes require egregiously complex application logic
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937
    because FIND_IN_SET, and any other solution such as the more complex LIKE one, do not scale

    unable to utilize an index, they get slower and slower as the number of rows increases

    plus, updates and deletes require egregiously complex application logic

    so what would be a better solution? lets say I append each id number in quotes via the application logic and then use the IN clause - the IN clause would still have the same scalability issues?
    Last edited by ozzii; 09-21-09 at 09:02.

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You should have your list of customer ids in a table as separate rows. This will be more expandable and much faster as it can use the indexes. I don't know what your database holds so I can't give you a complete answer but look up first normal form for more info. As it stands your system may work fine but if the system expands and you get more users and customers then you'll soon run into problems.

  7. #7
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by mike_bike_kite
    You should have your list of customer ids in a table as separate rows. This will be more expandable and much faster as it can use the indexes. I don't know what your database holds so I can't give you a complete answer but look up first normal form for more info. As it stands your system may work fine but if the system expands and you get more users and customers then you'll soon run into problems.
    the customer ids are indeed held in a table and they form the primary key for that table. However if i want to run a query to retrieve some details e.g. firstname, lastname, email etc for a list of customers and all i have is there id numbers eg 101, 102, 109, 111 ... In this instance how would you run a query to retrieve this information? Using the IN or FIND_IN_SET seem like the most obvious to me unless i ran invidual select queries on each id number however that would take forever if i have list of hundred ids.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in that case, IN ( list ) is exactly what you want here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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