Table joins with lookup table not working

[error123]

Hi,
I have a cath_procedure table with a procedure_name field that has to be stent-coronary.
Than I have an Event_cath table that keeps the date field for the cath event. Than I have a lookup table LU_CPT_COdes where I can query the CPT code, fields in this table Cptcode and cptdesc., but the lookup table is not related to the others. I woul dlike to join them all together, that I would get everyone who had a "sten-coronary" from the Cath_Procedures table and also whom had CPT code 92980000 or 90290011.
I am not getting the correct number.

SELECT Demographics.Last_Name, Demographics.First_Name, Demographics.Patient_ID, Event_Cath.Date_of_Cath, Cath_CPT_Codes.CPT_Code, Cath_CPT_Codes.Description, Cath_Procedures.Procedure_Name, Cath_CPT_Codes.SDKCode
FROM ((Demographics INNER JOIN Event_Cath ON Demographics.SS_Patient_ID = Event_Cath.SS_Patient_ID) INNER JOIN Cath_Procedures ON Event_Cath.SS_Event_Cath_ID = Cath_Procedures.SS_Event_Cath_ID) INNER JOIN Cath_CPT_Codes ON Event_Cath.SS_Event_Cath_ID = Cath_CPT_Codes.SS_Event_Cath_ID
GROUP BY Demographics.Last_Name, Demographics.First_Name, Demographics.Patient_ID, Event_Cath.Date_of_Cath, Cath_CPT_Codes.CPT_Code, Cath_CPT_Codes.Description, Cath_Procedures.Procedure_Name, Cath_CPT_Codes.SDKCode
HAVING (((Event_Cath.Date_of_Cath) Between [Start_Date] And [End_Date]) AND ((Cath_CPT_Codes.CPT_Code)="92980000") AND ((Cath_Procedures.Procedure_Name)="Stent - Coronary"));


Sometimes one person can have both coated and non coated stents.

Thank you!!

 

[Golom]

Just reformatting a bit

SELECT DISTINCT 
       D.Last_Name
     , D.First_Name
     , D.Patient_ID
     , E.Date_of_Cath
     , C.CPT_Code
     , C.Description
     , P.Procedure_Name
     , C.SDKCode

FROM ((Demographics As D 
       INNER JOIN Event_Cath      As E ON D.SS_Patient_ID    = E.SS_Patient_ID) 
       INNER JOIN Cath_Procedures As P ON E.SS_Event_Cath_ID = P.SS_Event_Cath_ID) 
       INNER JOIN Cath_CPT_Codes  As C ON E.SS_Event_Cath_ID = C.SS_Event_Cath_ID

WHERE E.Date_of_Cath Between [Start_Date] AND [End_Date] 
  AND C.CPT_Code IN ('92980000','90290011')
  AND P.Procedure_Name = 'Stent - Coronary'

You state that ... procedure_name field that has to be stent-coronary but your code has "Stent[COLOR=black yellow] [/color]-[COLOR=black yellow] [/color]Coronary". The versions with spaces before and after the hyphen and without them will not match.

You also said that the CPT_Code needs to be 92980000 or 92980011 so I've included an IN clause rather than the "=" that you had.

You were grouping by every field in the select clause so I dropped the GROUP BY and included a DISTINCT predicate instead.

The HAVING clause didn't have any aggregate functions so I converted it to a WHERE clause.

Do the coated and non-coated stents have different codes? If they do you will need to incorporate those codes.

Finally, you say that you are not getting the correct number. Are you getting too many? Not enough? The wrong ones?

 

[error123]

Hi!!!

That is exaactly what I got 58.

But how would I incorporate the description field being Stent Placement Single Vessel LC, LD, RC and "Stent; Bare Metal?

Here I am grouping on "Stent*" to give me the description Stent; Bare Metal:

SELECT Demographics.Last_Name, Demographics.First_Name, Demographics.Patient_ID, Event_Cath.Date_of_Cath, Cath_CPT_Codes.Description, Cath_Procedures.Procedure_Name, Cath_CPT_Codes.SDKCode
FROM ((Demographics INNER JOIN Event_Cath ON Demographics.SS_Patient_ID = Event_Cath.SS_Patient_ID) INNER JOIN Cath_Procedures ON Event_Cath.SS_Event_Cath_ID = Cath_Procedures.SS_Event_Cath_ID) INNER JOIN Cath_CPT_Codes ON Event_Cath.SS_Event_Cath_ID = Cath_CPT_Codes.SS_Event_Cath_ID
GROUP BY Demographics.Last_Name, Demographics.First_Name, Demographics.Patient_ID, Event_Cath.Date_of_Cath, Cath_CPT_Codes.Description, Cath_Procedures.Procedure_Name, Cath_CPT_Codes.SDKCode
HAVING (((Event_Cath.Date_of_Cath) Between [Start_Date] And [End_Date]) AND ((Cath_CPT_Codes.Description) Like "*Bare*") AND ((Cath_Procedures.Procedure_Name)="Stent - Coronary"));

So, I guess I woul dliek to see CPT code: 92980000 = Stent; Bare Metal
and 90290001 = CPTDesc from the lookup table for that would be: DE Stent.

Thank you!

 

[error123]

Hi!!!

This CPT code - but this is a supply code in my CPT lookup table brings in the "Stent;BareMetal":

SELECT Demographics.Last_Name, Demographics.First_Name, Demographics.Patient_ID, Event_Cath.Date_of_Cath, Cath_CPT_Codes.CPT_Code, Cath_CPT_Codes.Description, Cath_Procedures.Procedure_Name
FROM ((Demographics INNER JOIN Event_Cath ON Demographics.SS_Patient_ID = Event_Cath.SS_Patient_ID) INNER JOIN Cath_CPT_Codes ON Event_Cath.SS_Event_Cath_ID = Cath_CPT_Codes.SS_Event_Cath_ID) INNER JOIN Cath_Procedures ON Event_Cath.SS_Event_Cath_ID = Cath_Procedures.SS_Event_Cath_ID
GROUP BY Demographics.Last_Name, Demographics.First_Name, Demographics.Patient_ID, Event_Cath.Date_of_Cath, Cath_CPT_Codes.CPT_Code, Cath_CPT_Codes.Description, Cath_Procedures.Procedure_Name
HAVING (((Event_Cath.Date_of_Cath) Between [Start_Date] And [End_Date]) AND ((Cath_CPT_Codes.CPT_Code)="1481173") AND ((Cath_Procedures.Procedure_Name)="Stent - Coronary"))
ORDER BY Event_Cath.Date_of_Cath;


Thanks!!

 

[error123]

Hi Golom,

The Cath_CPT_Codes CPTCode field is designed as a text field. Would you know how to query that to get data out of the table.
Since CPT codes are numbers and when I put them to the criteria they do not return any rows.
CPTdesc is OK, that return rows, because that is text.
example: CPTCode:92980000

SO in one field I have acriteria as ....Cath_CPT_Codes.Description) Like "*Bare*") and CPTCode = 9298000 <----query brakes because it is a text field in the table!


...AND ((Cath_Procedures.Procedure_Name)="Stent - Coronary"));


Thank you!!