>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!

Would you try this :

"SELECT cf.FriendlyName, inf.Description, cnt.ItemCount " & _
" FROM force ADSDropConfigurations AS cf " & _
" LEFT OUTER JOIN ADSCounts AS cnt on cf.DropNo = cnt.DropNo" & _
" left outer join ADSItemInfo AS inf on cf.DropNo = inf.DropNo" & _
" WHERE cf.StationNo=0 AND cnt.StationNo=0"

PS: You can also use shape syntax.
Cetin