Solved

SQL JOIN help needed

Posted on 2012-03-26
6
301 Views
Last Modified: 2012-03-26
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.

Example:
Sport        Ball Needed
Football    Basketball
Baseball    Frisbee

Obviously those aren't the correct results that belong on those lines, and the same thing is happening (with different data :) ) in my query.

Query:

select
      ent.LAST_NAME,
      ent.FIRST_NAME,
      resp.eligibility,
      resp.responseCode,
      ins.POL_ID as memberID,
      ent.CASE_NUM,
      ins.PAYSRC_ID,
      ins.EXPIRES_DATE,
      ins.PRIORITY,
      ent.SOC_SEC_NUM,
      CONVERT(CHAR(10),ent.DOB,101) as DOB,
      emp.SORT_NAME as SAI_NAME,
      ent.PRIM_SUB_UNIT,
      resp.rejectReasonCode,
            '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.'
            END
from resp
      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
group by
ent.LAST_NAME,
ent.FIRST_NAME,
resp.eligibility,
resp.responseCode,
ins.POL_ID,
ent.CASE_NUM,
ins.PAYSRC_ID,
ins.EXPIRES_DATE,
ins.PRIORITY,
ient.SOC_SEC_NUM,
ent.DOB,
emp.SORT_NAME,
ent.PRIM_SUB_UNIT,
resp.rejectReasonCode


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?

Thanks!
0
Comment
Question by:fcsIT
6 Comments
 

Author Comment

by:fcsIT
ID: 37766577
Oh yeah, I know there is a line in my query that is "and ins.PAYSRC_ID in (100, 150)."  That's because I only want to see records returned that match that, so in my example above, Medical B should not have been returned.
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37766602
in your where clause

and ins.PAYSRC_ID in (100, 150)

will return all PAYSRC_ID = 100 or 150

How can it return 800?
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37766607
Never mind.  We were typing at the same time.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 13

Expert Comment

by:Ashok
ID: 37766626
Use only 2 tables and only one join

inner join ins on ins.POL_ID = resp.memberID

then check if resp.eligibility returns "Medical B" or not?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37766642
Without knowing more about the tables involved and the data structure of those tables it is difficult to tell, and your table names are so short that they don't provide much help either.

But then again, neither does the example, since you didn't use the actual fields which appear to contain invalid results.  It is most likely because one or more of the tables should be joined on two fields, not just one.  Or, you may need to create a subquery that joins two tables, and then join that to the others.

Personally, I would create a RejectionReason table that includes the Rejection codes and the descriptions, and use a left join to join that to the resp.rejectReasonCode field to get your rejection description.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 37766749
My best guess is that the joins:

from resp
      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

are missing one or more join criteria.  That is, the JOIN on at least one table should be based on at least two columns not just one, like so:

from resp
      inner join ins on ins.POL_ID = resp.memberID AND ? --AND ??
      inner join ent on ent.ID = ins.CLIENT_ID AND ? --AND ??
      inner join emp on emp.ID = ent.SAI_ID

That's also why you seem to need "GROUP BY".

SELECT DISTINCT should perform a LOT better than the GROUP BY, but hopefully once the table join(s) are fixed, you don't need either one :-) .
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now