> Well, I'm running 3.23.54 on Red Hat 7.3. Given this, how in the world > do I accomplish the following: > > I have these tables: > dacspriv - with dacspriv_id,dacspriv_name,short_name > users - with user_id,username > dacs_access - with dacsaccess_id,dacspriv_id,user_id > > I need to be able to return a list of dacspriv.short_name where > user.user_id IS NOT in dacs_access but ONLY for that user_id (I have > over 1700 users with multiple mappings in dacs_access). > > > > susan > > > -----Original Message----- > From: Ryan McDougall [mailto:[EMAIL PROTECTED] > Sent: Friday, May 30, 2003 12:11 PM > To: mysql > Subject: Re: Join problem > > >> Short answer is mysql does not do sub-selects (i.e., a select inside >> of a select). The join part is not this issue. > > Wouldn't this depend on the version... I thought the newest versions, > 4.x+, supported sub-selects. > > Ryan > Ok let's see:
select dacspriv_name,short_name from dacspriv,users LEFT JOIN dacs_access on dacs_access.user_id = users.user_id where dacs_access.user_id is NULL and users.user_id = WHATEVER The key is "dacs_access.user_id is NULL" While I haven't tried it with your data, I've used this in the past. For speed recommend user_id's in all tables be indexed. Obviously replace 'WHATEVER' with the userID value. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]