I'm storing inside a column a listing of states which are separated by commas:
Such as this: 1,2,3,4,5,6,7,8.. and so forth, just their IDs.
I Quickly am carrying out a query to obtain all of the rows which have the condition using the ID 8, and delay pills work once the listing of states has couple of products.
This is actually the query and also the table:
mysql> select id_partner, name, states from partner where 8 IN (states); +------------+----------------+--------------------+ | id_partner | name | states | +------------+----------------+--------------------+ | 1 | Inmo Inmo | 8,9,10 | | 2 | Foto Piso | 8,9,10,11,12,13,14 | | 4 | PARTNER 001-A | 8 | | 6 | EnAlquiler | 8 | | 7 | Habitaclia.com | 8,43,50 | +------------+----------------+--------------------+ 5 rows in set (0.00 sec)
When the column states consists of 10 IDs separated by comma it'll work, but when it's 50 or even more it won't work any longer. Within the above result it won't show the row which has many states, such as the one with ID 8.
Any idea? I'm by using this method of not needing to create another table in order to save the relation between your partner and also the states, or must i do this better?
That isn't the way the IN clause works--you should utilize the FIND_IN_SET function to look comma separated values in one column:
SELECT * FROM partner WHERE FIND_IN_SET(8, states) > 0
Reasonably, you shouldn't be storing comma delimited data. It's name is denormalized data, and can be challenging to obtain info on specific values inside the commas.
Your string is really a single value, not multiple values. It will happen contain commas, but it is still just one string.
The need for a string inside a number context is obtained from the key number numbers. Quite simply, the number worth of '123,456,789' is 123. MySQL ignores all figures beyond the first non-digit. (This is actually the behavior in MySQL, other databases can behave in a different way).
IN( ) predicate enables you to definitely rival multiple values, but make them separate SQL expressions. For example within the following two queries, only the first returns anything:
SELECT * FROM partner WHERE 5 IN ( 1, 2, 3, 4, 5 ); SELECT * FROM partner WHERE 5 IN ( '1,2,3,4,5' );
This shows one of the numerous explanations why you should not make use of a string with comma-separated elements and expect it some thing like it's actually a assortment of separate values.
The greater design within this situation would be to keep states for any given partner in another table, where each association from a partner along with a condition is on the separate row.
create table partner_states ( id_partner int not null, id_state int not null, primary key (id_partner, id_state) );
Then populate it:
insert into partner_states (id_partner, id_state) values (1, 8), (1, 9), (1, 10);
You'll be able to query for partners that match a particular condition easily:
select id_partner, name, states from partner p join partner_states s on p.id_partner = s.id_partner where s.id_state = 8
Querying for partners that don't match a condition that you can do by having an outer join:
select id_partner, name, states from partner p left outer join partner_states s on p.id_partner = s.id_partner and s.id_state = 8 where s.id_state is null