If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > how do i equate multiple values against multiple values returned in a GROUP_CONCAT

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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;

help with query for 1-1 and 1 to many table
and
my query is only returning single values

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' ), 
Reply With Quote
  #2 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 20,000
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?
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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:

how do i create an if clause within a mySQL query sub

thanks again everyone for your kind attention.

warmest and sincerest regards

Andreea
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On