CODEWITHSUNDEEP
mysql
Ben
Ben

Reputation: 69

Query with OR on 2 tables

What I am trying to do is list if an agency nurse 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

Upvotes: 0

Views: 68

Answers (1)

Welbog
Welbog

Reputation: 60458

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.

Upvotes: 3

Related Questions