Select one value of multiple values from database

Question

I store multiple values in VendorIDs field and I want to SELECT by using WHERE one value of my multiple values.

Ex :

Table

| OrderIDs | VendorIDs | CustomerIDs |
--------------------------------------
|    1     | 2, 3, 4   |      01     |
|    2     | 1, 2, 4   |      02     |
|    3     | 1         |      03     |
|    4     | 2, 3,     |      04     |
--------------------------------------

Code

SELECT * FROM orders WHERE VendorIDs = 2

When I selected VendorIDs = 2 it will be displayed by only first of values = 2.

| OrderIDs | VendorIDs | CustomerIDs |
--------------------------------------
|    1     | 2, 3, 4   |      01     |
|    4     | 2, 3,     |      04     |
--------------------------------------

And this is what I want it should be displayed like this :

| OrderIDs | VendorIDs | CustomerIDs |
--------------------------------------
|    1     | 2, 3, 4   |      01     |
|    2     | 1, 2, 4   |      02     |
|    4     | 2, 3,     |      04     |
--------------------------------------

Possible duplicate of http://stackoverflow.com/questions/5611715/where-value-in-column-containing-comma-delimited-values — Matt, Jan 09 '16 at 02:44
It might be easier if your `vendorIDs` had two columns .. `vendId` and `orderId` so that you'd have a single vendor ID associated with a a single order id. Then you have multiple colums .. "Ven id 1 - Order 2" Ven id 2 - Order 2" "Ven id 3 - Order 2" So you can see we assigned Ven 1,2,3 all to order 2 here. That's a multidemensional database. — Zak, Jan 09 '16 at 02:45
SELECT * FROM orders WHERE FIND_IN_SET(2, VendorIDs) it's worked, problem solved thank you so much. — Butter Toast, Jan 09 '16 at 03:02

score 1 · Answer 1 · answered Jan 09 '16 at 02:48

First, you have the wrong data structure. You should not be storing multiple ids in a singe string field. Here are some reasons:

Numeric ids should be stored as numbers, not strings.
Each column should contain one data item, rather than multiple items.
Ids should have foreign key constraints.
SQL has a great data structure for lists. It is called a table, not a string column.

That said, sometimes you are stuck with other people's bad design decisions.

If so, you can use find_in_set():

where find_in_set(2, replace(vendorids, ' ', '')) > 0

Alternatively, you can use like:

where concat(', ', vendorids, ', ') like concat(', %', 2, ', %)

+1 I recommend a reading of Chapter 2 of Bill Karwin's excellent book [**SQL Antipatterns: Avoiding the Pitfalls of Database Programming**](http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557). Chapter 2 is available in the "Look Inside" on Amazon. — spencer7593, Jan 16 '16 at 04:29

score 0 · Answer 2 · answered Jan 09 '16 at 02:46

0

If your column type is text then you can do a text match using the LIKE keyword with the string "% 2,%" (noting the whitespace and comma as a delimiter. For example

SELECT * FROM Orders where VendorIDs LIKE "% 2,%"

Better still, if you're using these ID's to look anything else up, store them in a separate table and JOIN. You can then use Group_Concat(VendorId) and a GROUP BY VendorId to aggregate the results into a similar format.

answered Jan 09 '16 at 02:46

developerjack

1,173
6
15

1

What if the result is the first result? Is there going to be a space in front of the number? – Matt Jan 09 '16 at 02:50
Good point; You may have to use `LIKE "2,%" OR LIKE "% 2,%";` thus catering for 'leading two or only two'. Again, its a horrible query hack which is better solved by an improved schema. – developerjack Jan 09 '16 at 03:17

score 0 · Answer 3 · answered Jan 09 '16 at 02:47

If you are certain that your IDs are stored in comma separated format as shown, you could do something like this:

declare vendorId int;
SELECT * FROM orders WHERE VendorIDs LIKE CONCAT('%', vendorId, ',%')

This should work fine, but it will deny any index usage (starting %) and a table scan will happen (noticeable differences will appear when your Orders table is very big and your query does not narrow the result).

Normally, your VendorIDs should be stored in a X table, to allow indexing:

**OrderVendor**
Id
OrderId
VendorId

Select one value of multiple values from database

3 Answers3