this question is a follow up to the question i raised in;
help with query for 1-1 and 1 to many table
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
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.
$jobs_cat ="'1', '2','3', '4','5','6','7','8', '9' ,'10'";
function worker_general_search ( $Co_lo= null, $jobs_cat=null, $limit=null )
$select = " SELECT
jb.job_category_sort " ;
$from = "
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
BY user_id ) AS jb
ON jb.user_id = u.user_id
$where = " WHERE u.live = '1'" ;
$where .= " AND cl.country_id = '$Co_lo'";
$where .= "
AND jb.job_category_sort IN ( $jobs_cat) ";
below is a sample of the tables :
CREATE TABLE users(
user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
membership_type CHAR(3) NOT NULL,
PRIMARY KEY (user_id)
CREATE TABLE countrylist
country_id MEDIUMINT UNSIGNED NOT NULL,
country VARCHAR(60) NOT NULL,
INDEX country_id ( country_id, country ),
INDEX countrylist (country, country_id ),
UNIQUE KEY (country)
CREATE TABLE countrylocation(
country_id VARCHAR(60) NOT NULL,
user_id MEDIUMINT UNSIGNED NOT NULL,
INDEX country_id (country_id, user_id ),
INDEX user_id (user_id, country_id )
The values in teh countrylist
(1 , 'African' ),
(2 , ' Albanian' ),
(3 , 'Saudia Arabia ' ),
(4 , ' Armenia' ),
(5 , ' Australia' ),
(6 , ' Austria' ),