138

I have 2 tables in my database. One is for orders, and one is for companies.

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?

The first query returns:

name
---------------
Company 1
Another Company
StackOverflow

The second query only returns:

name
---------------
Company 1

Why is this, why does the first query return all the companies, but the second query only returns the first one?

gen_Eric
  • 223,194
  • 41
  • 299
  • 337

4 Answers4

107
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)

In PostgreSQL, you could cast the string into array (or store it as an array in the first place):

SELECT  name
FROM    orders
JOIN    company
ON      companyID = ANY (('{' | attachedCompanyIDs | '}')::INT[])
WHERE   orderID = 1

and this would even use an index on companyID.

Unfortunately, this does not work in MySQL since the latter does not support arrays.

You may find this article interesting (see #2):

Update:

If there is some reasonable limit on the number of values in the comma separated lists (say, no more than 5), so you can try to use this query:

SELECT  name
FROM    orders
CROSS JOIN
        (
        SELECT  1 AS pos
        UNION ALL
        SELECT  2 AS pos
        UNION ALL
        SELECT  3 AS pos
        UNION ALL
        SELECT  4 AS pos
        UNION ALL
        SELECT  5 AS pos
        ) q
JOIN    company
ON      companyID = CAST(NULLIF(SUBSTRING_INDEX(attachedCompanyIDs, ',', -pos), SUBSTRING_INDEX(attachedCompanyIDs, ',', 1 - pos)) AS UNSIGNED)
vlio20
  • 8,955
  • 18
  • 95
  • 180
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 3
    Thanks for the explanation. I didn't realize the attachedCompanyIDs field was casted to an INT. Is there any way around this in MySQL? `FIND_IN_SET` works, but doesn't uses indexes, and may be slow with a lot of info in the Company table. – gen_Eric Nov 11 '10 at 15:43
  • 1
    Can you explain that update? What exactly does that do, because it seems to work. – gen_Eric Nov 11 '10 at 16:16
  • 1
    @Rocket: it strips `pos` items from the beginning of the `CVS` and casts the rest into integer. – Quassnoi Nov 11 '10 at 16:20
  • 12
    Thumbs up (y) for `10 things in MySQL (that won’t work as expected)` – Smile Feb 28 '14 at 06:12
  • @Quassnoi, Why do you write `CROSS JOIN`? Aren't they [all the same](https://dev.mysql.com/doc/refman/5.0/en/join.html) in MySQL? – Pacerier Apr 18 '15 at 07:08
  • @Pacifier: because I need a cross join here. – Quassnoi Apr 18 '15 at 08:58
  • @DJDave: it's back on. Apparently StackOverflow is a better expired domain registration reminder service than that of my hosting provider. – Quassnoi Mar 01 '17 at 14:06
  • what to do when both sides we have coma seperated values. like company ID is also like 1,3,4,5 etc and attachedCompanyIDs is also coma seperated like 4,5,2,1 ???? can we do that please ?? – Amani Nov 01 '18 at 13:10
14

attachedCompanyIDs is one big string, so mysql try to find company in this its cast to integer

when you use where in

so if comapnyid = 1 :

companyID IN ('1,2,3')

this is return true

but if the number 1 is not in the first place

 companyID IN ('2,3,1')

its return false

Haim Evgi
  • 123,187
  • 45
  • 217
  • 223
5

To get the all related companies name, not based on particular Id.

SELECT 
    (SELECT GROUP_CONCAT(cmp.cmpny_name) 
    FROM company cmp 
    WHERE FIND_IN_SET(cmp.CompanyID, odr.attachedCompanyIDs)
    ) AS COMPANIES
FROM orders odr
Mosh Feu
  • 28,354
  • 16
  • 88
  • 135
1

because the second query is looking for rows with the id's 1 OR 2 OR 3, the first query is looking for a one of the comma delimited values to exist in companyID,

and another problem here is you aren't joining the tables on a common key in your where so you are going to get a mutation of rows that = count(table1) * count(table2);

Your problem really exists with part 2 of my answer. (with your second query)

superfro
  • 3,327
  • 1
  • 18
  • 14
  • There are more rows in both tables than I am showing. In both tables, there the ID of the user you are logged in as, would joining on that help? – gen_Eric Nov 11 '10 at 15:30
  • Well you only need to change anything if your first query isn't returning the expected results. If the first query is returning the results that you want then there really isn't a problem. I thought you were just curious why the 2 don't show the same result. – superfro Nov 11 '10 at 15:34
  • @superfro, I am curious as to why the 2 don't show the same result. – gen_Eric Nov 11 '10 at 15:39
  • your second query is using a where IN (values) where the 'values' part come from the table, and its a string. The string is being evaluated as a bool true which = 1 which is why it only shows the first row. – superfro Nov 11 '10 at 15:43
  • @superfro, that's not true. Quassnoi's answer explains the issue. – gen_Eric Nov 11 '10 at 15:45
  • @superfro, yes IN expands to 1 OR 2 OR 3, but if I wasn't passing IN a string it would work. `SELECT name FROM company WHERE companyID IN (1,2,3)` works. – gen_Eric Nov 11 '10 at 15:46
  • I didn't realize it could just be taking the first value instead... none the less the problem is with it being a string. – superfro Nov 11 '10 at 15:50
  • 1
    If you are worried about performance, you should probably think about changing your database structure. You could add a joint table that contains 2 values, order_ID and company_ID instead of using the comma delimited list in the order table. This would allow you to select name from company left join order_companies on company.company_ID = order_companies.company_ID left join orders on order_companies.order_ID = order.order_ID where orders.order_ID = 1; This would use indexes. – superfro Nov 11 '10 at 16:05
  • Yeah, I probably should have made a joint table, that would have been a better idea. – gen_Eric Nov 11 '10 at 17:33