[RESOLVED] Ado distinct left join

**aviben** · May 12th, 2014, 03:22 PM

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

**dilettante** · May 12th, 2014, 03:42 PM

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.

**techgnome** · May 12th, 2014, 04:37 PM

You have to put the DISTINCT in the correct location... IN FRONT of the FIELD(s) you want distinct...as part of the SELECT clause.

Select DISTINCT * from someTable

http://www.w3schools.com/sql/sql_distinct.asp
http://technet.microsoft.com/en-us/l.../ms187731.aspx (Example C)
http://www.techonthenet.com/sql/distinct.php
http://blog.sqlauthority.com/2007/12...on-discussion/

-tg

**aviben** · May 13th, 2014, 01:38 AM

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

**Joacim Andersson** · May 14th, 2014, 12:00 AM

Moved to the Database Development forum.

**FunkyDexter** · May 14th, 2014, 05:29 AM

Yes:-
Select Count (Distinct SomeField)
From SomeTable

**dilettante** · May 15th, 2014, 05:42 AM

Not every DBMS accepts predicates in aggregate functions though.

Thread: [RESOLVED] Ado distinct left join