oracle - sql join and minus -

i seem having problem getting query work. know i'm close. here's copy of er diagram

i think close achieving want code, invalid identifier when trying run it. think because practice being changed somehow after joining, getting invalid identifier on row 5?

select   staffid, staff_firstname, staff_surname, practice.practice_name, practice.practice_city staff join practice on staff.practiceid = practice.practiceid minus select   staffid, staff_firstname, staff_surname, practice.practice_name, practice.practice_city staff    role = 'gp'; 

basically i'm trying use minus construct find practices not employ gp , include information such city , practice_address.

i can use minus construct find out how many staff not have role of gp so:

select   staffid, staff_firstname, staff_surname staff minus select   staffid, staff_firstname, staff_surname staff    role = 'gp'; 

where results:

   staffid staff_firs staff_surn __________ __________ __________          8 nyssa      thornton          9 mona       bradshaw         10 gloria     pena 

i'm struggling use join minus construct information gp's practice address , city etc.

any appreciated!

the second select, after minus, referring columns practice table - doesn't join it:

select staffid, staff_firstname, staff_surname,   practice.practice_name, practice.practice_city staff join practice on staff.practiceid = practice.practiceid minus select staffid, staff_firstname, staff_surname,   practice.practice_name, practice.practice_city staff join practice on staff.practiceid = practice.practiceid    role = 'gp'; 

that isn't going give want though, remove rows staff gps, not trace of practices have gps - non-gp staff @ practices still shown.

if don't want remaining staff details need include columns practice table in select lists, , minus give want (and gordon linoff has shown 2 alternatives minus in case). if want remaining staff details can use not-exists clause rather minus - like:

select s.staffid, s.staff_firstname, s.staff_surname,   p.practice_name, p.practice_city staff s join practice p on s.practiceid = p.practiceid not exists (   select 1   staff s2   s2.practice_id = p.practice_id   , s2.role = 'gp ); 

this similar gordon's second query has join staff details. again, if don't want those, use gordon's simpler query.

you use aggregate check, or analytic function if you've learned abput those, save having hit tables twice.