'SQL query on denormalized tables
I have these two below mentioned denormalized tables with out any data constraints. Records_audit will not have duplicate audit_id based rows though table doesn't have any constraints.
I will need SQL query to extract all fields of records_audit with an addtional matching column refgroup_Name from second table using matching condition of AuditID from both tables, printedCount greater than 1 and R_status as 'Y'. I tried to do with left join but it is selecting all records.
Can you help to correct my query? I tried with this below query but its selecting all unwanted from second table:
SELECT a.*, d.refgroup_Name
from Records_audit a
left join Patients_audit d ON ( (a.AUDITID=d.AUDITID )
and (a.printedCount> 1)
AND (a.R_status='Y')
)
ORDER BY 3 DESC
Records_audit:
| AuditID | record_id | created_d_t | patient_ID | branch_ID | R_status | printedCount |
|---|---|---|---|---|---|---|
| 1 | Img77862 | 2020-02-01 08:40:12.614 | xq123 | aesop96 | Y | 2 |
| 2 | Img87962 | 2021-02-01 08:40:12.614 | xy123 | aesop96 | Y | 1 |
Patients_audit:
| AuditID | dept_name | visited_d_t | patient_ID | branch_ID | emp_No | refgroup_Name |
|---|---|---|---|---|---|---|
| 1 | Imaging | 2020-02-01 11:41:12.614 | xq123 | aesop96 | 976581 | finnyTown |
| 1 | EMR | 2020-02-01 12:42:12.614 | xq123 | aesop96 | 976581 | finnyTown |
| 2 | Imaging | 2021-02-01 12:40:12.614 | xy123 | himpo77 | 976581 | georgeTown |
| 2 | FrontOffice | 2021-02-01 13:41:12.614 | xy123 | himpo77 | 976581 | georgeTown |
| 2 | EMR | 2021-02-01 14:42:12.614 | xy123 | himpo77 | 976581 | georgeTown |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
