Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    35

    Question Unanswered: Need a hand with a 'Contains' type of idea

    Hi there,

    quick question, given this table:

    Code:
    CREATE TABLE `A` (
      `S` int(32) unsigned NOT NULL default '0',
      `M` int(32) unsigned NOT NULL default '0',
      PRIMARY KEY  (`S`,`M`),
      KEY `M_index` (`M`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8
    With potential sample data such as:

    [
    { 1, 2 }
    { 1, 3 }
    { 2, 4 }
    { 2, 3 }
    { 1, 5 }
    ]

    I would like to devise a query that can efficiently return all S values that contain a certain set: S, G<X,Y,Z> where G is inclusive

    Example, if I were to ask for all M=2,5 it should return 1 since there exists sets where the values are (boldfaced above):

    {1,2}
    {1,5}

    Likewise, asking for M=3,4,5 should fail, since there is no S value that contains M=3, M=4 and M=5...question makes sense?

    Something like this possible with MySQL?

    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT S FROM A WHERE M IN ( 2, 5 )
    GROUP BY S HAVING COUNT(*) = 2

    SELECT S FROM A WHERE M IN ( list of values )
    GROUP BY S HAVING COUNT(*) = number_of_items_in_list
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    35

    Thumbs up

    Brilliant. I hadn't thought of checking for a count in a HAVING clause.

    Thanks as always!!

Posting Permissions

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