Distinct ...and multiple records in SQL query

[irinnew]

Good Morning!

I need to extract unique records from PROF table based on DESC =”C” OR “F C” criteria. And connect with Main Table in order to get ID (unique)from the main table. In other words I do not need multiple records for the same ID.

Prof table and Main table do not have common field. For this purpose I use Link table which has a common fields with both tables above

So…prof table and link table have KEY
Main table and link table have KEY1

When I didn’t use DISTINCT keyword I got a huge result (assume it is because of multiple records for key fields). Therefore I used distinct now but the result ( RGS table) is still huge and have multiple records) ! Therefore I have a feeling that I used DISTINCT in a wrong manner.


PROC SQL;
CREATE TABLE RGS AS
(select distinct m.KEY1
,m.ID
,p.DESC
,p.KEY
,l.KEY
,l.KEY1
FROM prof p
JOIN l.link l
on l.KEY=p.KEY
JOIN m. main
on m.KEY1=l.KEY1
WHERE (p.DESC="C" OR
p.DESC="F C")
);
quit;
RUN;


What is my mistake? wHY distinct DOES NOT WORK AS I EXPECT?

Thank you very much in advance.

Irin

 

[DotNetGnat]

you should say:

WHERE (p.DESC='C' OR
p.DESC='F C')

-DNG