CODEWITHSUNDEEP
sqloracle-database
user3357649
user3357649

Reputation: 53

sql join and minus

I seem to be having problem getting a certain query to work. I know I'm so close. Here's a copy of my er diagram

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!

Upvotes: 1

Views: 992

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

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.

if you don't want the remaining staff details you only need to include the columns from the practice table in the select lists, and the minus would then give you what you want (and Gordon Linoff has shown two alternatives to minus in that case). If you do want the remaining staff details then you can 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
);

This is similar to Gordon's second query but has an extra join to staff for the details. Again, if you don't want those, use Gordon's simpler query.

You could also use an aggregate check, or could probably do something with an analytic function if you've learned abput those, to save having to hit the tables twice.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Your original query only operates on the level of "staff", not "practice". I would be inclined to solve this using aggregation:

select  p.practice_name, p.practice_city
from staff s join
     practice p
     on s.practiceid = p.practiceid
group by p.practice_name, p.practice_city
having sum(case when s.role = 'GP' then 1 else 0 end) = 0;

Or, even better:

select p.*
from practice p
where not exists (select 1
                  from staff s
                  where s.practiceid = p.practiceid and s.role = 'GP'
                 );

I think this is the simplest and most direct interpretation of your question.

Upvotes: 1

Related Questions