user3357649 user3357649 - 7 months ago 23
SQL Question

sql join and minus

I seem to be having problem getting a certain query to work. i know im so close. Here's a copy of my er diagram enter image description here

I think i am so close to achieving what i want to do with this code, only i get invalid identifier when trying to run it. i think its because the practice is being changed somehow after joining, as i am only getting invalid identifier on row 5?

SELECT staffid, staff_firstname, staff_surname, practice.practice_name, practice.practice_city
from staff
join practice on staff.practiceid = practice.practiceid
MINUS
SELECT staffid, staff_firstname, staff_surname, practice.practice_name, practice.practice_city
from staff
where role = 'GP';


Basically i'm trying to use the minus construct to find practices which do not employ a GP and include some information such as the CITY and practice_address.

I can use the minus construct to find out how many staff do not have the role of gp like so:

SELECT staffid, staff_firstname, staff_surname
from staff
MINUS
SELECT staffid, staff_firstname, staff_surname
from staff
where role = 'GP';


where i get the results

STAFFID STAFF_FIRS STAFF_SURN
__________ __________ __________
8 NYSSA THORNTON
9 MONA BRADSHAW
10 GLORIA PENA


i'm struggling to use the join with the minus construct to get information about the GP's practice address and city etc.

Any help would be greatly appreciated!

Answer

The second select, after the minus, is referring to columns from the practice table - but it doesn't join to it:

SELECT   staffid, staff_firstname, staff_surname, practice.practice_name, practice.practice_city
from staff
join practice on staff.practiceid = practice.practiceid
MINUS
SELECT   staffid, staff_firstname, staff_surname, practice.practice_name, practice.practice_city
from staff
join practice on staff.practiceid = practice.practiceid
where    role = 'GP';

That isn't going to give you what you want though, it will just remove the rows for staff that are GPs, not all trace of practices that have any GPs - non-GP staff at all practices will still be shown.

You either need to only inlcude the columns from the practice table in the select lists; or use a not-exists clause rather than a minus - something like:

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

Or you could probably do something with an analytic function if you've learned abput those, to save having to hit the tables twice.

Comments