Solved

SQL JOIN help needed

Posted on 2012-03-26
6
308 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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:
Scott Pletcher 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Star schema daily updates 2 33
Need help with a query 3 36
Change this SQL to get all nodes 3 36
SQL Syntax 6 36
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

685 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