[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

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.

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

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

Moved to the Database Development forum.

Yes:-
Select Count (Distinct SomeField)
From SomeTable

Not every DBMS accepts predicates in aggregate functions though.