asked
Managers who are not assigned to a supplier
Using the Ramazon exercise, provide SQL statement(s) and the result set that is a list of the suppliers (ID and Name) and their account managers (ID, Name, and Date Assigned). Additionally, please include in the result set all managers that have not been assigned to a Supplier. Please sort the result list by relationship length in ascending order. SELECT s.sup_id "Supplier ID", s.sup_name "Supplier Name", a.mgr_id " Manager ID", a.mgr_fname || ' ' || a.mgr_lname " Manager Name", a.mgr_date_assigned "Manager Date Assigned" FROM talbert3_r_supplier s, talbert3_acct_manager a WHERE s.mgr_id <> a.mgr_id ORDER BY (a.mgr_date_assigned); Something I am doing is causing it to return all of my entries.
Oracle recommends performing JOINs in the FROM clause rather than the WHERE clause - [
http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm][1] If you want all managers (with or without suppliers), plus all suppliers that have managers, you'll probably want to use a LEFT OUTER JOIN (I've reversed the order in which the tables are referenced) FROM talbert3_acct_manager a LEFT OUTER JOIN talbert3_r_supplier s ON a.mgr_id = s.mgr_id ORDER BY (a.mgr_date_assigned); [1]:
http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm
I completely agree with Ken regarding using the JOIN syntax. The reason your original query returned everything was because of your WHERE condition. "WHERE s.mgr_id a.mgr_id" will return every row for which another row exists with a different Id. If there are at least 2 different values, every row will be returned.
Is there a particular reason you reversed the order? It worked, I am just curious. Thank you, Taylor
17 People are following this question.
