Hi,
The error code which you shared is not related to distinct clause, It might be related to aggregation function if you have used any in the first part of the query.
….left join ( SELECT DISTINCT
ID
FROM table) table 2
on table1.ID=table2.ID
To brief you on the use of distinct clause in subquery below is the sample illustration,
I tried to join two tables with one table as a actual table and other table as subquery result. I used distinct clause to extract distinct records in the subquery and then joined with actual table. It worked fine for me without any errors.
For example,
I have Employee table with employee details and employeeid as primary key.
EmpId Name Salary
1 XXX 10
2 YYY 20
3 ZZZ 30
4 VVV 40
I have Sales table with sales details of each employee and saleid as primary key.
Saleid Empid Prodid
1 1 10
2 1 10
3 2 11
4 2 12
5 3 15
6 3 15
Now I want to know the salary of only employees who made sales. For this, I executed the below query by using distinct clause in subquery and it worked for me,
"Select a.empId, a.salary from employee a inner join (select distinct empid from sales) b on a.empId = b.empId"
Actual Result,
Empid Salary
1 10
2 20
3 30
Hope this helps!!
