SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    It's possible, but you would be far better normalising the data. Is that an option?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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..

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by venkat6134 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    please do a SHOW CREATE TABLE for your tables, so that we have something to discuss rather than just generalities...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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;

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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..

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i think you should normalize
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Evangelist venkat6134's Avatar
    Join Date
    Jul 2010
    Posts
    454
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •