Query problem

Question

Many thanks once again in advance.

What I am trying to do is list if an agency nuse works in hospital H2 or has a qualification Q3 or both

I have two tables Agency_A & Qualification

Agency_A     
Nurse      Hospital
Thomas     H1  
Taylor     H2  
Evans      H3  
Davies     H2  

Qualification
Nurse      Qualification
Thomas     Q2
Taylor     Q3
Evans      Q4
Freeman    Q3

So I'm looking for

|TAYLOR  |                          {as she works in H2 and has qualification Q3}
|FREEMAN |                          {as she has qualification Q3}
|DAVIES  |                          {as she works in H2 etc

Any help with this would be great.

Answer 1

This ought to cover it:

SELECT
  COALESCE(Qualification.Nurse, Agency_A.Nurse)
FROM (
  SELECT DISTINCT Nurse 
  FROM Agency_A 
  WHERE Hospital = 'H2') Agency_A
LEFT OUTER JOIN (
  SELECT DISTINCT Nurse 
  FROM Qualification 
  WHERE Qualification = 'Q3') Qualification
ON Agency_A.Nurse = Qualification.Nurse

This covers the case where a nurse is in one table but not the other.