SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    ontario
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    search a SET column?

    I have a table with two columns, the first column is of type VARCHAR and the second of type SET.

    column1(VARCHAR) column2(SET)
    person1 group1
    person2 group2
    person3 group1, group2

    I want to retrieve everyone in "group1"...

    Code:
    SELECT * FROM mytable WHERE column2 = 'group1';
    Which returns only person1 and not person1 and person3 as I want.

    I have searched through the manual and found several ways to search for data matching several criteria (ANY, IN, SOME, ALL) but not "the other way around".

    I guess what I'm really asking is how do I search within a column of type SET?

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I think that you can use FIND_IN_SET to do this.

    Code:
    SELECT * FROM mytable WHERE FIND_IN_SET('group1', column2)
    Yours, Erik.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  3. #3
    chown linux:users\ /world Hartmann's Avatar
    Join Date
    Aug 2000
    Location
    Houston, TX, USA
    Posts
    6,455
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Just as a side note, SET and FIND_IN_SET are only supported on MySQL so if you are planning on porting this to another database any time then I would consider using something else.

  4. #4
    SitePoint Member
    Join Date
    Jan 2005
    Location
    ontario
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Solution

    Lilleman: Thank you, this worked.
    Hartmann: I did consider that SET is not considered by all to be "the right way" to do things; I did not know however that MySQL is the only database to support SET. Unfortunately with this fix the rest of my program/script now works wonderfully and I have already invested too much time to change everything. I will now hope that my script/database can serve it's purpose long enough that I do not need to make these changes.

    Some helpful links I found in solving this issue:
    http://dev.mysql.com/tech-resources/...-datatype.html
    http://www.vbmysql.com/samplecode/setdatatypesql.html

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DRB
    interesting that they are both by the same guy!
    (coincidence? i don't think so )
    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
  •