?
Solved

SQL JOIN help needed

Posted on 2012-03-26
6
Medium Priority
?
315 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 48

Expert Comment

by:Dale Fye
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:
Scott Pletcher earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

770 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