Distict question

[michael bradly]

I'm trying to have distinct records output and am having problems.

COLUMN TreatmentDesc NOPRINT
SELECT DISTINCT AnimalId, AnimalType, ExamID, ExamDate, TreatmentDesc From VT_animal
INNER JOIN VT_Exam USING(AnimalID)
INNER JOIN VT_ExamDetail USING(ExamID)
 INNER JOIN VT_Treatment USING(TreatmentID)
 WHERE INSTR(TreatmentDesc,'VITAMIN') > 0 OR INSTR(TreatmentDesc,'DENTAL') > 0 
 

What is the best means of hiding TreatmentDesc from view as well as preventing it from affecting the DISTINCT command?
Regards, Michael

[Mark Spritzler]

"What is the best means of hiding TreatmentDesc from view as well as preventing it from affecting the DISTINCT command?"
Um, how about not including it in your Select?
Mark

[michael bradly]

Well if that is the case, then what would be the best means of extracting information from TreatmentDesc since I need information from that table in order to determine which animals have had a dental or vitamin examine, without showing duplicates of those who have had both at the same time?
I was under the impression that DISTICT could prevent duplicate output lines, and hence by not printing out the TreatmentDesc I could obchieve that goal.

Originally posted by Mark Spritzler:
"What is the best means of hiding TreatmentDesc from view as well as preventing it from affecting the DISTINCT command?"
Um, how about not including it in your Select?
Mark

[Mark Spritzler]

Wait a second, I thought in oracle you could do this.

SELECT      treatmentdesc, 
   DISTINCT animalid, 
            animaltype, 
            examid, 
            examdate
FROM        vt_animal 
INNER JOIN  vt_exam        USING(animalid)
INNER JOIN  vt_examdetail  USING(txamid) 
INNER JOIN  vt_Treatment   USING(treatmentid) 
WHERE       INSTR(treatmentdesc,'VITAMIN') > 0 
   OR       INSTR(treatmentdesc,'DENTAL')  > 0 

Then it would not be a part of the distinct values, but still be in the resultset.
Mark

[SJ Adnams]

trying to do something like this I think...?

 
SELECT AnimalId, AnimalType, ExamID, ExamDate
from
(SELECT DISTINCT AnimalId, AnimalType, ExamID, ExamDate, TreatmentDesc 
From ... WHERE ... );