Need Help

• Ashwini K-402578

  Grasshopper

  Points: 10

  More actions

  January 30, 2007 at 2:54 am

  #62529

  There are 2 tables Users which containing userid and other user related data and other table is UserRoles which contains userid and roleid.

  I want to fetch users who do not have any roles assigned.

  i want to fetch large amount of data so if i use "not exists" or"NOT IN" then it affects performance  of query very badly.

  So please help me to get the required result with optimized query

  Thanks In advance,

  Ashwini K.

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  January 30, 2007 at 4:41 am

  #686400

  hi

  try this......

  select u.userid from users u left outer join userroles ur on u.userid = ur.userid

  where ur.roleid is null.

  "Keep Trying"

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736642

  More actions

  January 30, 2007 at 7:31 am

  #686441

  Be sure you have indexes on the join columns as well.