[RESOLVED] Ado distinct left join
Hello,
I'm using this SQL statement to left join 2 Excel ranges and it works fine
Code:
Sql = "SELECT * FROM " & rs1 & " AS T1 LEFT JOIN " & rs2 & " AS T2 ON T1." & CommomVar & "=T2." & CommomVar
I want the joining to refer to the distinct rows of T2. The statement
Code:
Sql = "SELECT * FROM " & rs1 & " AS T1 LEFT JOIN " & rs2 & " AS DISTINCT T2 ON T1." & CommomVar & "=T2." & CommomVar
returns an error
Any idea?
Thanks
Avi
Re: Ado distinct left join
What about something like...
Code:
SELECT * FROM range1 AS T1 LEFT JOIN (SELECT DISTINCTROW * FROM range2) AS T2
ON T1.var = T2.var
Not tested.
Re: Ado distinct left join
Re: Ado distinct left join
Thanks. It works well!
Is there any way to count the number of DISTINCT rows. I have tried unsuccessfully
Code:
Set rs22 = ObjConnection.Execute("SELECT COUNT( *) FROM (SELECT DISTINCT * " & rs2 & " AS T2)") ' Get nb of rows from recordset
Avi
Re: [RESOLVED] Ado distinct left join
Moved to the Database Development forum.
Re: [RESOLVED] Ado distinct left join
Yes:-
Select Count (Distinct SomeField)
From SomeTable
Re: [RESOLVED] Ado distinct left join
Not every DBMS accepts predicates in aggregate functions though.