My query below is returning too many results. Something in the joins is wrong. I'm getting results that have data on the same row that doesn't match other data on that row.
Sport Ball Needed
Obviously those aren't the correct results that belong on those lines, and the same thing is happening (with different data :) ) in my query.
ins.POL_ID as memberID,
CONVERT(CHAR(10),ent.DOB,101) as DOB,
emp.SORT_NAME as SAI_NAME,
'Reject Reason' = CASE
WHEN rejectReasonCode = 57 THEN 'Invalid/Missing date(s) of service.'
WHEN rejectReasonCode = 58 THEN 'Invalid/Missing date of birth.'
WHEN rejectReasonCode = 62 THEN 'Date of Service not within allowable inquiry period.'
WHEN rejectReasonCode = 63 THEN 'Date of Service is in the future.'
WHEN rejectReasonCode = 72 THEN 'Member ID is invalid.'
WHEN rejectReasonCode = 75 THEN 'Subscriber/Insured not found.'
WHEN rejectReasonCode = 76 THEN 'Duplicate Subscriber/Insured ID number.'
inner join ins on ins.POL_ID = resp.memberID
inner join ent on ent.ID = ins.CLIENT_ID
inner join emp on emp.ID = ent.SAI_ID
where resp.fileDate = '2012-03-15'
and ins.PAYSRC_ID in (100, 150)
and ins.PRIORITY <> 9
Example Resultset: (I only included a few columns here due to the number of columns in my query. The columns here represent the problem.)
eligibility memberID case_num paysrc_id
Medical A 123456789 12345 100
Medical B 123456789 12345 100
In this example, row one is right and row two is wrong. Row two should have a paysrc_id of 800, which is the correct ID of Medical B, but it is showing 100, which is the ID for Medical A. The eligibility and paysrc_id columns come from different tables.
Does anyone know where I messed up in my query that's causing this?