SQL query on denormalized tables-CodePudding

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

CodePudding user response：

A left join will give you all records in the "left" table, that is the from table. Since you have no where clause to constrain the query you're going to get all records in Records_audit.

See Visual Representation of SQL Joins for more about joins.

If your intent is to get all records in Records_audit which have an R_status of Y and a printedCount > 1, put those into a where clause.

select ra.*, pa.refgroup_name 
from records_audit ra  
left join patients_audit pa on ra.auditId = pa.auditId
where ra.printedCount > 1
  and ra.r_status = 'Y'
order by ra.created_d_t desc

This will match all records in Records_audit which match the where clause. The left join ensures they match even if they do not have a matching Patients_audit record.

Other notes:

Your order by 3 relies on the order in which columns were declared in Records_audit. If you mean to order by records_audit.created_d_t write order by a.created_d_t.
If your query is making an assumption about the data, add a constraint to make sure it is true and remains true.