Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25

    how do i equate multiple values against multiple values returned in a GROUP_CONCAT

    Hello Everyone,

    this question is a follow up to the question i raised in;

    http://www.dbforums.com/mysql/168810...any-table.html
    and
    http://www.dbforums.com/mysql/168804...ml#post6576766

    the issues raised there was how to return multiple values in a query where the database had tables that enjoyed 1-1 and 1-many relationships. The solution presented by R937 was to use subqueries in the From Clause and a GROUP_CONCAT in the SELECT query to return those values.

    This solution worked perfectly!.

    The issues now however is this; how can i equate multiple values against the multi values returned in the GROUP_CONCAT.

    it might be easier to explain if i give a real world example.

    in my SQL query i used the GROUP_CONCAT (via a subquery) to return multi values ie 1,2,3,4,5,6,7,8, (these numbers represent the ID numbers of langauges spoken by a member,i.e English, French , German etc).

    i now want to draft a SQL query that will enable me to search the database for all members who speak any one of a number of specified langugages eg: English,French , Italian etc.

    so in essence the GROUP_CONCAT returns values like: 1,2,3,4,5,6,7,8,

    and i want to equate these values to search values like: 1', '2','3', '4','5','6',

    i tried using the IN , ALL and ANY OPERATORS to equate the values but each of these can only compare one value against many; none can compare multipile values against multiple returns.

    Below is a copy fo my search query. i suspect that i will have to use a subquery in my WHERE CLAUSE . However i am not clear how i will draft a loop that loop around each of the returned values agasint each of the submitted values.

    PHP Code:

      $Co_lo
    =3;
    $jobs_cat    ="'1', '2','3', '4','5','6','7','8', '9' ,'10'";


    function 
    worker_general_search (   $Co_lonull,  $jobs_cat=null,    $limit=null   )

        
         {             
                 global 
    $dbc;
                          
        
        
    $select "      SELECT 
                     u.user_id, 
                      u.first_name, 
                      cl.country_id,
                      col.country,  
                      jb.job_category_sort  " 
    ;            

    $from   

                     FROM 
                              users u  
                 LEFT OUTER  JOIN   countrylocation cl
                        ON u.user_id = cl.user_id
                 LEFT OUTER  JOIN  countrylist col  
                        ON cl.country_id = col.country_id   
                     LEFT OUTER  JOIN 
                    ( SELECT user_id,
                        GROUP_CONCAT(job_id) AS job_category_sort
                       FROM worker_category
                     GROUP
                         BY user_id ) AS jb
                    ON jb.user_id = u.user_id  
                        "
    ;
              
                      
    $where      "     WHERE   u.live  = '1'" ;              
      
               
                      if(!empty(
    $Co_lo) )
                    {
                      
    $where .= "    AND    cl.country_id = '$Co_lo'";
                    }              
                
                    
            if(!empty(
    $jobs_cat) )    
                   {
                     
    $where .= "   
                                          AND  jb.job_category_sort IN ( 
    $jobs_cat)  ";
                 
                           } 
    below is a sample of the tables :



    PHP Code:


      CREATE TABLE users
    (
      
    user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
      
    membership_type CHAR(3NOT NULL

    PRIMARY KEY (user_id)

    }

     
    CREATE TABLE countrylist
      country_id  MEDIUMINT UNSIGNED NOT NULL

      
    country    VARCHAR(60NOT NULL,
      
    INDEX country_id country_id,  country  ), 
      
    INDEX countrylist (countrycountry_id ), 
      
    UNIQUE KEY (country)


      
    CREATE TABLE countrylocation(
      
    country_id   VARCHAR(60NOT NULL,
      
    user_id  MEDIUMINT UNSIGNED NOT NULL,
      
    INDEX country_id (country_iduser_id ), 
      
    INDEX user_id (user_idcountry_id  )
    ); 

    The values in teh countrylist 


        
    ('African' ),
        (
    ' Albanian' ),
        (
    'Saudia Arabia ' ),
        (
    ' Armenia' ),
        (
    ' Australia' ),
        (
    ' Austria' ), 

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do your equate testing in the subqueries on the individual values before the GROUP_CONCAT is done, that way you don't have to work with multiple values strung together
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    Hello again everyone

    And special thanks to R937 for the advice given below

    Quote Originally Posted by r937 View Post
    do your equate testing in the subqueries on the individual values before the GROUP_CONCAT is done, that way you don't have to work with multiple values strung together
    I am not entirely clear how to do it for several reasons.

    1. not clear how to do the testing in the subquery on individual values before then doing a further test to do a GROUP_CONCAT.

    below is my attempt to draft the query. As you can see, i have ran a-ground at the point of trying to do the second sub query for the GROUP CONCAT.



    PHP Code:

       SELECT 
                     u
    .user_id
                      
    u.first_name,
                     
    jbjob_id
                      
                      FROM 
                              users u  
                LEFT OUTER  JOIN 
                    
    SELECT user_id,job_id
                       FROM worker_category
                    WHERE  u
    .live  '1'  
                      
    if(!empty($Co_lo) )
                    {
                      
    $where .= "    AND   job_id IN ( '1', '2','3', '4','5','6','7','8', '9' ,'10'  )
                    }         
                
                    if Job_id     
                         ( SELECT user_id,
                        GROUP_CONCAT(job_id) AS job_category_sort
                       FROM worker_category
                     GROUP
                         BY user_id)
                         
                         
                       ) AS jb
                    ON jb.user_id = u.user_id              
                   WHERE   u.live  = '1' 
    I would really appreciate any advice/sample code on the correct way to draft the query.

    warm regards

    Andreea 115

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT u.user_id
         , u.first_name
         , jb.job_category_sort
      FROM users AS u
    LEFT OUTER
      JOIN ( SELECT user_id
                  , GROUP_CONCAT(job_id) AS job_category_sort
               FROM worker_category
              WHERE job_id IN ( 1,2,3,4,5,6,7,8,9,10  )
           ) AS jb
        ON jb.user_id = u.user_id
     WHERE u.live = 1
    the WHERE clause in the subquery occurs before the GROUP_CONCAT is performed

    simple, yes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2012
    Location
    London and Bucarest
    Posts
    25
    Quote Originally Posted by r937 View Post
    [code]the WHERE clause in the subquery occurs before the GROUP_CONCAT is performed

    simple, yes?
    Hello again everyone

    thank you so much to R937 (without his kind help, i would not have been able to build my search query) .

    i just want to confirm that it worked perfectly.

    i am very grateful for the assistance.

    the only obstacle now before me, with regards to my search function, is how to place the

    PHP Code:

    AND  job_id IN 1,2,3,4,5,6,7,8,9,10  
    within an "if clause" so that it can be left blank if the user does not wish to restrict the query. i however suspect that this is a PHP question, so i have sent my question to the PHP Section of this forumn:

    http://www.dbforums.com/php/1688269-...ml#post6577392

    thanks again everyone for your kind attention.

    warmest and sincerest regards

    Andreea

Posting Permissions

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