[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    RE: Join problem
From:       Susan Ator <SAtor () npr ! org>
Date:       2003-05-30 19:59:25
[Download RAW message or body]

Well, it's not blowing up on me and returning a jillion records. Trouble is,
it's also not returning any records at all.

Thanks for the suggestion, though. I'll keep plugging away at it.

susan

-----Original Message-----
From: William R. Mussatto [mailto:mussatto@csz.com]
Sent: Friday, May 30, 2003 3:42 PM
To: mysql@lists.mysql.com
Cc: Susan Ator
Subject: RE: Join problem


> 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:mcdougrs@yahoo.com]
> 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/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]