Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2011
    Posts
    14

    Unanswered: A Challenging SQL Question

    Hey guys,

    I have a table t with two columns: entryID and catID with the following sample data:
    Code:
    entryID  catID
    1          10
    2          10
    2          11
    3          11
    3          15
    3          16
    4          10
    4          11
    I want a select statement to retrieve those entryIDs that their catIDs are in a list, let's say: 10 and 11. In this case it should bring entryIDs 2 and 4.
    Actually, we don't know in advance the list of the catIDs, so maybe we ask to bring those entryIDs that has catIDs 11 and 15 and 16. In this case it should bring the entryID 3.
    The way that we can provide the list is a comma separated string like "10,11" in the first case and "11, 15, 16" in the second case.

    Any idea?
    Regards,
    dbGuyo

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Code:
    with catlist (catid) as (
       values (10), (11)
    ) 
    select entryid 
    from t
    where t.catid in (select catid from catlist) 
    group by t.entryid
    having count(distinct t.entryid) = (select count(*) from catlist);
    That is ANSI SQL and here is a SQLFiddle example: SQL Fiddle
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    very nice link, shammat, very nice

    i find it fascinating how often people post ordinary-seeming questions in the ANSI SQL forum and get perfectly good solutions... that just happen to fail in their particular database system

    any bets on whether the original poster will be able to run your solution this time?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2011
    Posts
    14
    Thank you so much Shammat for your response.
    I think I should have posted my question in the MySQL section because currently, I am using it.
    Anyway, as far as I know, MySQL doesn't support With clause, so, how can you make an equivalent for your code for MySQL?

    Thank you again,
    dbGuyo

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    called it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select entryid 
      from t
     where t.catid in ( put your comma-delimited list here ) 
    group 
        by t.entryid
    having count(*) = put the number of list items here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2011
    Posts
    14
    Thank you so much r937. It works great. BTW, I just saw your book in the Amazon. It looks a great collection.

  8. #8
    Join Date
    Jul 2011
    Posts
    14
    Hi again,

    It doesn't work when we have a list of one catID. For example if I ask for catID=10, then it should retrieve just entryID=1 but it retrieve 1, 2, 4.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12

  10. #10
    Join Date
    Jul 2011
    Posts
    14
    Thank you sooo much Shammat. Now it is perfect. I hope that it can pass for the high volume of records as well.
    Thank you again guys,
    So long

Tags for this Thread

Posting Permissions

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