I am migrating a VB application's database from MSDE SQL to a VFP database, and I'm embarrassed to say I am being whipped by one of the more involved SQL statements.

I wish I could simplify it into 2 queries, but the app' requeries it on a timer, so that wouldn't work.

The MSDE/SQL Server syntax is:

With rs
.ActiveConnection = frmStartup.cnLocal
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockReadOnly
.Source = _
"SELECT cf.FriendlyName, cn.[Description], cn.ItemCount " & _
"FROM ADSDropConfigurations AS cf " & _
"JOIN (SELECT distinct cnt.DropNo, cnt.ItemCount, inf.[Description] FROM ADSCounts AS cnt " & _
"JOIN ADSItemInfo AS inf " & _
"ON cnt.DropNo=inf.DropNo WHERE cnt.StationNo=0) AS cn " & _
"ON cf.DropNo = cn.DropNo " & _
"WHERE cf.StationNo=0"
.Open
End With

My best attempt at getting this to go through the VFP ODBC driver thus far is:

With rs
.ActiveConnection = frmStartup.cnLocal
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockReadOnly
.Source = _
"SELECT cf.FriendlyName, cn.Description, cn.ItemCount " & _
"FROM ADSDropConfigurations AS cf LEFT OUTER JOIN " & _
"(SELECT distinct cnt.DropNo, cnt.ItemCount, " & _
"inf.Description FROM ADSCounts AS cnt, ADSItemInfo AS inf " & _
"WHERE cnt.DropNo=inf.DropNo AND cnt.StationNo=0) AS cn " & _
"ON cf.DropNo = cn.DropNo WHERE cf.StationNo=0"
.Open
End With

I guess I'm not handling the subquery correctly. I wanted to move both of the join conditions into the WHERE clauses but couldn't figure out how to write this with the subquery mixed up in there.

TX!