thanks shawn it seems to be working but i forgot to add that i need it for a single user ID, in other words it must bring back all user ids in the user table if they do not exist for that user in the buddylist.
so what i've tried is this: SELECT u.* FROM users u LEFT JOIN buddylist bl ON u.userID = bl.userID WHERE u.isactive =1 AND u.userid!='$userid' AND bl.userID is null but this still returns some results that exist in the buddyList table. [EMAIL PROTECTED] wrote: >Angelo Zanetti <[EMAIL PROTECTED]> wrote on 06/08/2005 12:06:51 PM: > > > >>Hi guys. >> >> > > > >>I'm having a problem deciding whether a left join is suitable for what i >>want to do. >> >> > > > >>I have two tables >> >> > > > >>A Users >>-userID >>-isactive >> >> > > > >>B BuddyList >>-userID >>-buddyID >> >> > > > >>what i want to do is to get all the users from A that don't exist as a >>buddyID for a user (buddyList) also the user must be active (isactive=1) >> >> > > > >>but i cant get the correct result. >>Is the LEFT JOINcorrect for this operation or should i try using the NOT >>EXISTS command? >> >> > > > >>TIA >> >> > > > >>Angelo >> >> > >Yes, the LEFT join is the correct choice for your query: > >SELECT u.UserID >FROM Users u >LEFT JOIN BuddyList bl > ON u.userID = bl.userID >WHERE u.isactive =1 > AND bl.userID is null; > >It's that last term (and bl.userID is null) that detects the non-matched >users across the LEFT JOIN. > >Shawn Green >Database Administrator >Unimin Corporation - Spruce Pine > >
