CODEWITHSUNDEEP
phpmysql
Butter Toast
Butter Toast

Reputation: 105

Select one value of multiple values from database

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

Upvotes: 1

Views: 990

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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, ', %)

Upvotes: 1

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

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

Upvotes: 0

developerjack
developerjack

Reputation: 1213

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.

Upvotes: 0

Related Questions