Hi all,
I have a table, with a column having the data as comma(,) separated values…
Now i want to write a query to fetch the rows related to one of the comma(,) separated values…
Data in table COLUMN is;
1ST ROW----- A,B,C,D
2ND ROW----- V,S,R
3RD ROW----- C,V
4TH ROW----- A,D
NOW I WANT TO SHOW THE ROWS BASED ON THE VALUE ‘C’
MEANS;
I WANT TO DISPLAY ONLY 1ST ROW AND 3RD ROW…
HOW TO WRITE A QUERY TO GET THE DATA…
THANKING YOU…
It’s possible, but you would be far better normalising the data. Is that an option?
I tried some trails to get it.
But i failed to retrieve the rows based on the value presented in the column…
Help me to get it…
r937
March 31, 2011, 12:00pm
4
venkat6134:
Help me to get it…
WHERE FIND_IN_SET(‘C’,daColumn) > 0
but this is gonna get slower and slower as your table grows larger and larger
Thanks for your reply,
but is there any chance to use REGEXP or RLIKE.
or
any better way to retrive.(if it is having large table)
r937
March 31, 2011, 12:13pm
6
those will also be slow
the problem is your design, it breaks first normal form
the best thing you can do is redesign and normalize
At that time the table size is more na…
I have a table having the related cables under one group say ‘A’. and another column having cable list…
and i have another table having connectors,based on connectors related cables will be shown…for that i inserted that cable group_id in the connectors table…
How to design the tables for better use…
r937
March 31, 2011, 12:39pm
8
please do a SHOW CREATE TABLE for your tables, so that we have something to discuss rather than just generalities…
CREATE TABLE IF NOT EXISTS connector
(
sno
int(5) NOT NULL,
series
varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
manufacturer
varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
impedance
varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
gender_polarity
varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
orientation_mount
varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
method
varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
conn_id
varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
cable_group
varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
conn_image
varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE IF NOT EXISTS cable_desc
(
sno
int(5) NOT NULL,
cable_name
varchar(30) DEFAULT NULL,
cable_group
varchar(10) DEFAULT NULL,
cable_desc
varchar(1000) DEFAULT NULL,
type
varchar(30) DEFAULT NULL,
impedance
varchar(20) DEFAULT NULL,
frequency
varchar(20) DEFAULT NULL,
cable_template
varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
r937
April 1, 2011, 9:36am
10
that looks a bit weird, because every column is VARCHAR, and you don’t even have primary keys
okay, could you please tell us which column has the comma-delimited list?
In connector table, cable_group is having Group id’s.
In cable_desc table, cable_name is having names of the cables under each cable_group id’s.
In both tables, these two columns are having comma separated values…
r937
April 1, 2011, 9:59am
12
wow, i am ~really~ lost
if cable_group in connectors table has multiple values, does cable_group in cable_desc table also have multiple values?
i think you should “go back to square one” and completely redesign both tables, without using comma-delimited values in any column anywhere
cable_group column in connectors table has multiple values like; A,B,C. these are group id’s.
cable_name column in cable_desc table has multiple values like;
cable_name cable_group
145,174 A
185,205 B
219,306,.4lr C
r937
April 1, 2011, 10:28am
14
i think you should normalize
how to do this?
I don’t know much about database handling and normalization…
Give me some example regarding my problem with normalization…
Thanking you…
r937
April 1, 2011, 11:35am
16
sorry, i cannot, because i do not know your data
you really should take some database tutorials
fortunately, there are literally hundreds of normalization tutorials on teh interwebs