question

tcalbert93 avatar image
tcalbert93 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.
oraclehomework
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
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
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
2 Likes 2 ·
I reversed it so I could use the left outer join rather than a right outer join. Conceptually, it seems easier for people to grasp the left join rather than the right, so I make it a habit to avoid right joins whenever I can.
1 Like 1 ·
Is there a particular reason you reversed the order? It worked, I am just curious. Thank you, Taylor
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.