Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    73

    Question Unanswered: Alternative of not in clause ???

    Use ABC
    Select * from Menu
    Where Menu.MenuId Not In (Select Distinct MenuId from MenuByRoles)

    is there any other way to write above query ?? i don't want to use
    Not in clause

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    NOT EXISTS, EXCEPT, create a table that has the list of menuid's that do not exist in menubyroles table and then use IN.
    Dave

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by amitwadhawan123 View Post
    i don't want to use Not in clause
    why not?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2008
    Location
    Chennai
    Posts
    21
    Use ABC

    Select *

    From Menu M

    Left Outer Join MenuByRoles MBR
    On M.MenuId = MBR.MenuId

    Where MBR.MenuId is NULL

  5. #5
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Select * from Menu a
    Where not exists (Select Distinct MenuId from MenuByRoles b where a.MenuID = b.MenuID)

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by PMASchmed View Post
    Select * from Menu a
    Where not exists (Select Distinct MenuId from MenuByRoles b where a.MenuID = b.MenuID)
    what do you think would happen if you removed the DISTINCT?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    i know, i know... pick me!!!!

Posting Permissions

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