Use JOIN with multiple WHERE conditions and OR logic
I need to audit our provider roster. Pull every provider with their name, specialty, department name, hire date, and active status. Flag anyone who's inactive OR has a hire date after today — those are credentialing issues I need to resolve before the CMS review.
| Column | Type | Key |
|---|---|---|
| provider_id | INT | PK |
| first_name | TEXT | |
| last_name | TEXT | |
| specialty | TEXT | |
| department_id | INT | FK → dim_departments |
| hire_date | TEXT | |
| is_active | INT |
| Column | Type | Key |
|---|---|---|
| department_id | INT | PK |
| department_name | TEXT | |
| department_type | TEXT | |
| floor | INT | |
| bed_count | INT | |
| manager_name | TEXT |
Each hint you reveal reduces the XP you can earn. Try the query first.
Two-table join from providers to departments — no WHERE filter; Dr. Nguyen wants the full roster to scan herself.
Project the six columns she named (name fields, specialty, department name, hire date, active flag) directly from the joined rows.
Don't pre-filter the red flags — the audit is the human's job. Your job is to surface the joined roster cleanly.