Distinct keyword 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 from the main table.

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 empty ! 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?

Thank you very much in advance.

Irina

 

[irinnew]

Actually I got a huge table again which makes me think that my DISTINCT works in a wrong manner....

Could you please help me with it?

Thanks again

Irin

 

[ChrisW75]

If in doubt, split the query into separate queries, this will allow you to test each part.
I'm not sure, however I think you might want to put brackets around the field you want distinct values of. Personally I would fix my driving table first, extract it, then dedupe it in a safe and measured way (it's generally a good idea to examine duplicates to be sure what it is that you are dropping), then use this to join to your other tables. You can use the DBKEY= option to speed up your joins between SAS datasets and DBMS tables if that is a concern.