Mysql : FIND_IN_SET() vs IN()

Orders has this structure:

OrderID     |     attachedCompanyIDs
------------------------------------
   1                     1,2,3
   2                     2,4

And Company has this structure:

CompanyID      |        name
--------------------------------------
    1                 Company 1
    2                 Another Company
    3                 StackOverflow
    4                 Nothing

To get an order's companies names, 

I can do a query as such: SELECT name FROM orders,company WHERE orderID = 1 AND FIND_IN_SET(companyID, attachedCompanyIDs) 

That query works fine, but the following query does not.

 SELECT name FROM orders,company WHERE orderID = 1 AND companyID IN (attachedCompanyIDs) 

Why does the first query work but not the second one?

Explanation :

SELECT  name
FROM    orders,company
WHERE   orderID = 1
        AND companyID IN (attachedCompanyIDs)

attachedCompanyIDs is a scalar value which is cast into INT (type of companyID).

The cast only returns numbers up to the first non-digit (a comma in your case).

Thus,

companyID IN ('1,2,3') ≡ companyID IN (CAST('1,2,3' AS INT)) ≡ companyID IN (1)

MySQL: GROUP_CONCAT

By admin | August 24, 2014

0 Comment

MySQL GROUP_CONCAT Function
In this tutorial, we will learn how to use the MySQL GROUP_CONCAT function to concatenate strings from a group of values with various options.
The following illustrates the GROUP_CONCAT function:

MySQL

1	GROUP_CONCAT(DISTINCT expression ORDER BY {column_name | usinged_integer | expression} SEPARATOR separator);

The maximum length of the return string is 1024 by default.
You can extend the returned value’s length by setting the group_concat_max_len system variable at SESSION or GLOBAL level.
MySQL GROUP_CONCAT Examples
Let’s take a look at the countries table in the sample database.

MySQL

1 2 3 4 5 6 7 8 9

CREATE TABLE `countries` ( `country_id` INT(10) NOT NULL AUTO_INCREMENT, `country_name` VARCHAR(100) NULL DEFAULT NULL, `status` ENUM('Y','N') NULL DEFAULT 'Y', PRIMARY KEY (`country_id`) ) ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=1

To get all countries as a (,) string, you use the GROUP_CONCAT function as follows:

MySQL

1	SELECT GROUP_CONCAT(country_name) as country_list FROM countries;

To remove the duplicate country names, you have to add the DISTINCT clause as the following query:

MySQL

1	SELECT GROUP_CONCAT(DISTINCT country_name) as country_list FROM countries;

To sort the country names before concatenating other than ASC, you have to use the ORDER BY clause as follows:

MySQL

1	SELECT GROUP_CONCAT(country_name ORDER BY country_name DESC) AS country_list FROM countries;

To change the default separator of the returned string from a comma (,) to a any other, you have to use the SEPARATOR clause as the following query:

MySQL

1	SELECT GROUP_CONCAT(DISTINCT country_name ORDER BY country_name DESC SEPARATOR ', ') AS country_list FROM countries;

Let’s take a look at the states table associated with countries table:

MySQL

1 2 3 4 5 6 7 8 9 10

CREATE TABLE `states` ( `state_id` INT(10) NOT NULL AUTO_INCREMENT, `state_name` VARCHAR(100) NOT NULL, `country_id` INT(10) NOT NULL, `status` ENUM('Y','N') NOT NULL DEFAULT 'Y', PRIMARY KEY (`state_id`) ) ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=1

To get country name and all associated state names as concatenated string using the following query:

MySQL

1 2 3 4

SELECT country.country_name,GROUP_CONCAT(state.state_name SEPARATOR ', ') as state_list FROM states AS state INNER JOIN countries AS country ON country.country_id = state.country_id GROUP BY state.country_id;

To get country name and all associated state names with their status(By using CONCAT function) as concatenated string using the following query:

MySQL

1 2 3 4

SELECT country.country_name,GROUP_CONCAT(IF(state.status = 'Y', CONCAT(state.state_name, '( Active)'),  CONCAT(state.state_name, '( Inactive)')) ORDER BY state.state_name ASC SEPARATOR ', ') as state_list FROM states AS state INNER JOIN countries AS country ON country.country_id = state.country_id GROUP BY state.country_id;

To get country name and all associated state names with their status(By using CONCAT_WS function) as concatenated string using the following query:

MySQL

1 2 3 4

SELECT country.country_name,GROUP_CONCAT(CONCAT_WS(', ', state.state_name, IF(state.status = 'Y','Active','Inactive')) ORDER BY state.state_name ASC SEPARATOR ', ') as state_list FROM states AS state INNER JOIN countries AS country ON country.country_id = state.country_id GROUP BY state.country_id;

Post Views: 6,492