How to write a sql query for this?

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…

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)

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…

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;

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…

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

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…

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

:cool: