SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Jun 2007
    Posts
    358
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Selectively applying user ID in WHERE clause to entries

    Hi,

    I been going back and forth with IF and CASE and a whole lot of other suggestions found elsewhere trying to figure out how to filter out certain entries based on category and user ID. Assume that a table `user_posts` contains posts made by a lot of users. I want to get results restricted to the `user_ID`='MIRA76' for posts that are in categories 'CAT_64' or 'CAT_106'. However, I still want to get results by other users from other categories.

    TABLE `user_posts`

    post_ID category_ID user_ID content
    456FQ CAT_64 MEK90 TEXTUAL CONTENT
    754GD CAT_21 MEK90 TEXTUAL CONTENT
    616CZ CAT_106 MIRA76 TEXTUAL CONTENT
    714PA CAT_64 MIRA76 TEXTUAL CONTENT
    554WU CAT_26 RON32 TEXTUAL CONTENT
    451YD CAT_41 RON32 TEXTUAL CONTENT
    783TD CAT_21 MIRA76 TEXTUAL CONTENT
    156GL CAT_11 MEK90 TEXTUAL CONTENT
    274HD CAT_11 HOM92 TEXTUAL CONTENT

    In the example entries, for categories 'CAT_64' and 'CAT_106' only those by the `user_ID` 'MIRA76' is taken, but for other categories there is no such restriction. How do I go about doing this? Here those in green highlight are the ideal results.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    WHERE category_id IN ('CAT_64','CAT_106') AND user_id = 'MIRA76' 
       OR category_id NOT IN ('CAT_64','CAT_106')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Jun 2007
    Posts
    358
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This seems to do exactly what I want, my actual table contains 10k+ entities so it's difficult to go over it to make sure. So just to confirm, it will also fetch posts by `user_ID` MIRA76 in the category CAT_21, yes?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cssExp View Post
    ... it will also fetch posts by `user_ID` MIRA76 in the category CAT_21, yes?
    yes, posts by all users in that category
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Jun 2007
    Posts
    358
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great! Thank you very much :D


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
  •