Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL JOIN help needed

Posted on 2012-03-26
6
Medium Priority
?
321 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 70

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

610 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