Join problem???

[RAWC]

I have a table of procedures done on a patient. The patient can have one procedure with more than one diagnosis or modifier (left, right, bilateral, etc.) The modifier stored in the patient procedure table is the key value, when I instead need the description. Everything brings back what I need until I get to this darned modifier thing.
My query brings back 3 rows of data, when I only want two.
Here's my code, and after it what I get back, and what I want instead. Thanks in advance for any insight!!!!!

Select
pp1.service_item_id, pp1.service_item_desc, pp1.service_date, pp1.note,
pp1.diagnosis_code_id_1, pp1.diagnosis_code_id_2, pp1.diagnosis_code_id_3, pp1.diagnosis_code_id_4,
ct.description,
pm.description,
mod.description

from patient_procedure pp1, code_tables ct, provider_mstr pm, modifier_mstr mod
where pp1.person_id = <input parm of patient id>
and ct.code = pp1.place_of_service
and (ct.delete_ind <> 'Y')
and (ct.code not in ('11','12', '51', '09'))
and code_type = 'place_serv'
and pp1.provider_id = pm.provider_id
and ((pp1.modifier_id_1 = mod.modifier_id) or (pp1.modifier_id_2 = mod.modifier_id)
or (pp1.modifier_id_3 = mod.modifier_id) or (pp1.modifier_id_4 = mod.modifier_id))


I want my results like this:

29830 Arthroscopy, elbow, diagnostic 20080313 845.01 V43.64 Outpatient Hospital Alhadeff, Joseph E LT- LEFT SIDE 80- ASSISTANT SURGEON
------------------------------------------------
29826 Arthscpy shldr decompression 20080313 714.2 Ambulatory Surgical Center Alhadeff, Joseph E RT- RIGHT SIDE
------------------------------------------------


Instead of like this:


29830 Arthroscopy, elbow, diagnostic 20080313 845.01 V43.64 Outpatient Hospital Alhadeff, Joseph E LT- LEFT SIDE
------------------------------------------------
29830 Arthroscopy, elbow, diagnostic 20080313 845.01 V43.64 Outpatient Hospital Alhadeff, Joseph E 80- ASSISTANT SURGEON
------------------------------------------------
29826 Arthscpy shldr decompression 20080313 714.2 Ambulatory Surgical Center Alhadeff, Joseph E RT- RIGHT SIDE

 

[Crowley16]

can you re-write your sql using ansi join syntax and then post it back again?

e.g.

select
columns...
from
table A inner join
table B on a.colid = b.colid


--------------------
Procrastinate Now!

 

[SQLSister]

Are you using SQL Server 2000 or 2005? The method is different between them. And in any event it is usually better to do this manipulation in the user interface than in SQL Server.

And I agree with Crowley, you nmeed to start using the normal join syntax, use of this sysntax will get you introuble when you need to start using left joins. Make yourself a promise to never ever write another piece of code using this syntax. YOu will thank us for this advice when things start to get more complex.

"NOTHING is more important in a database than integrity." ESquared