Re: Syntax error (missing operator) in query expression

First, if it works for you in Query Analyzer,
then save that SQL as a passthrough and enjoy
how it "snaps back" the results (if that can work
for you).

Else, Jet query joins typically are

INNER JOIN
LEFT JOIN
RIGHT JOIN

plus, here's a snip from an old post
from John Viescas:

****quote****
The SQL Standard allows for both bracketed and non-bracketed syntax, but
requires brackets when the ON clause doesn't immediately follow the JOIN
clause. A FROM clause must consist of a <table-expression>.

<snip>

Personally, I find it much clearer to enclose each pair of base tables
inside parentheses, treat that as one "logical table", and then add the next
table and JOIN-ON clause. So, I would write it like this:

((((a inner join b on a.x = b.x)
inner join c on a.x = c.x)
inner join d on a.x = d.x)
left join e on a.x = e.x)
left join f on a.x = f.x
***unquote*****

this might do you.....

SELECT
dbo_mainCustomer_Florida.customerID,
qDRI2.tDRI2,
qDRISF.tDRISF,
qRRI.tRRI,
dbo_mainCustomer_Florida.agencyName,
dbo_mainCustomer_Florida.state,
dbo_mainCustomer_Florida.availCredits
FROM
dbo_mainCustomer_Florida
INNER JOIN
[SELECT
customerID,
COUNT(*) tDRI2
FROM
((dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
dri2_Offenders DRI2
ON
EV.password = DRI2.userpassword)
WHERE
Cdate(DRI2.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRI2
ON
dbo_mainCustomer_Florida.customerID = qDRI2.customerID
LEFT JOIN
[SELECT
customerID,
COUNT(*) tDRISF
FROM
((dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
drisf_Offenders DRISF
ON
EV.Password = DRISF.userpassword)
WHERE
cdate(DRISF.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qDRISF
ON
dbo_mainCustomer_Florida.customerID = qDRISF.customerID
LEFT JOIN
[SELECT
customerID,
COUNT(*) tRRI
FROM
((dbo_mainCustomer_Florida MC
INNER JOIN
dbo_evaluators2_Florida EV
ON
MC.customerID = EV.Customer_ID)
INNER JOIN
rri_Offenders RRI
ON
EV.Password = RRI.userpassword)
WHERE
Cdate(RRI.testDate)
BETWEEN #5/5/2005# AND #5/5/2006#
GROUP BY
customerID]. qRRI
ON
dbo_mainCustomer_Florida.customerID = qRRI.customerID


.

Relevant Pages

  • Re: SQL to Linq - Left, Right and Inner Joins
    ... recognize an inner join and thus, limit the effective amount of data transferred through the wire. ... As a cross-join can spawn millions of tuples, which are often not needed, the where clause will run slower than an ON clause in an inner-join set operation. ... That's also why I find it a big design flaw in linq that the only operator in the linq's join clause is an 'equals' operator, which makes the production of left/right joins awkward, yet these operations are often required. ... At least, for me, that helps to see the process as being a double translation, and to STOP thinking in SQL sets, while using LINQ, but to then think in terms of for-each sequences. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SQL to Linq - Left, Right and Inner Joins
    ... Remember that SQL is not about HOW TO ... is NO PROBLEM using a CROSS JOIN to do it, FOLLOWED by a WHERE clause. ... normally put in the ON clause of the INNER join. ...
    (microsoft.public.dotnet.languages.csharp)
  • Need some help with simple search query
    ... It is doable via stored procedure. ... NOW Put your main SELECT CLAUSE IN ONE Variable WITHOUT ... >FROM CliDrAppts.dbo.tblCliAppts CA INNER JOIN ... NOW Execute Dymanic SQL. ...
    (microsoft.public.sqlserver.programming)
  • Re: Sql 2005 with Access 2003 sort order issue
    ... You resolved my issue and increased my SQL knowledge. ... followed by SQL-Server; especially when you add a TOP 100 PERCENT clause. ... The TOP clause is usually used in conjonction with an Order By to retrieve ... FROM dbo.Tbl_Drums INNER JOIN ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Basic Question on SQL SELECT Performance
    ... Defintely Inner join is better then Outer Joins. ... In the Query, is that you are only filtering the rows between the two tables ... I hope this is an appropriate group to ask a question about basic SQL ... The item from the WHERE clause when selecting from ...
    (microsoft.public.sqlserver.datamining)