Refine SQL query to particular row on table

Question

Brief Explanation of problem:

I'm currently trying to create a report (RDL file) in visual studio that gets all the phone calls for a specific case, and prints them out in 2 tables that look like this: Current tables . My issue is that when I run the report, it is grabbing information from every case, instead of the particular case I'm currently in.

Here is the query I'm using to get the information for the tables:

SELECT 

FilteredPhoneCall.regardingobjectidname, 

FilteredPhoneCall.icms_contactname, 

FilteredPhoneCall.owneridname,

FilteredPhoneCall.icms_callendtime, 

FilteredPhoneCall.icms_informationgathered, 

Filteredicms_impcase.icms_casenumber

FROM 

FilteredPhoneCall, 

Filteredicms_impcase

WHERE FilteredPhoneCall.regardingobjectidname 

LIKE '%' + Filteredicms_impcase.icms_casenumber + '%'

How should I change my query so that it specifies that all information should be from a single case. Here are some pictures of the 2 tables I'm trying to join for clarity:

FilteredPhoneCall table and Filteredicms_impcase

Should I be using an inner join statement? Also let me clarify my thought process on why I'm using the LIKE statement is because of how the rows are in the system, the FilteredPhoneCall.regardingobjectidname is formatted like this: Name of company / CaseID, I need the 2nd half of that value, the CaseID in order to join the tables together to get the specific case value.

A)whether my syntax is working the way how I think it's working?

B)should I use a more specific identifier, like an INNER JOIN?

score 0 · Answer 1 · answered May 11 '18 at 22:40

Don’t use “,” for joining 2 tables. It is worse than cross joins

Convert into Inner Join like below:

SELECT 

FPC.regardingobjectidname, 

FPC.icms_contactname, 

FPC.owneridname,

FPC.icms_callendtime, 

FPC.icms_informationgathered, 

FC.icms_casenumber

FROM 

FilteredPhoneCall FPC 

INNER JOIN Filteredicms_impcase FC

ON FPC.regardingobjectid = FC.icms_casenumberid

WHERE FPC.regardingobjectidname 

LIKE '%' + FC.icms_casenumber + '%'

score 0 · Answer 2 · answered May 11 '18 at 23:41

I would transform the "regarding object id name" and perform the join explicitly:

SELECT 

FilteredPhoneCall.regardingobjectidname, 

FilteredPhoneCall.icms_contactname, 

FilteredPhoneCall.owneridname,

FilteredPhoneCall.icms_callendtime, 

FilteredPhoneCall.icms_informationgathered, 

Filteredicms_impcase.icms_casenumber

FROM 

FilteredPhoneCall

INNER JOIN Filteredicms_impcase

ON SUBSTRING_INDEX(FilteredPhoneCall.regardingobjectidname,'/',-1) =

Filteredicms_impcase.icms_casenumber

See this post for details: SQL SELECT everything after a certain character

Refine SQL query to particular row on table

2 Answers2