Rick Faircloth wrote: > I'm using MySQL (4.0) version, which doesn't support > subqueries, so I need to rewrite this as a join query...how would I do that? > > Select CAL.EmailAddress > from classroom_access_list CAL > where CAL.EmailAddress > not in (Select S.EmailAddress from staff S) > > What I'm after is a query that gives me all the email addresses in the > classroom_access_list table and the staff table without duplicates... > > I've been working on the join approach, but those things just > elude my logic for some reason...can't wait until I'm ready to move to > MySQL 4.1, which supports subqueries...
SELECT DISTINCT COALESCE(s.EmailAddress, cal.EmailAddress) EmailAddress FROM classroom_access_list cal FULL OUTER JOIN staff s ON s.EmailAddress = cal.EmailAddress Or something like that. K. -- Keith Gaughan, Developer Digital Crew Ltd., Pembroke House, Pembroke Street, Cork, Ireland http://digital-crew.com/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184609 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
