Re: Query with left outer join all of a sudden won't work?

Tech-Archive recommends: Speed Up your PC by fixing your registry

I don't see the problem, sorry.


Vanderghast, Access MVP

"JenniferW" <JenniferW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4487EAFD-6B5F-47F2-843C-A2C736A6A3E9@xxxxxxxxxxxxxxxx
I did change the join between the queries to an inner join and it worked.
It
just doesn't work if it is a left or right outer join.

Thanks

"Michel Walsh" wrote:

That query in itself should work unless one of the query involved in the
join is, in itself, with some problem (and the error message is to be
applied to it, rather than on the top most query calling it).

Does

SELECT * FROM [qryFarmer Contracts]

and

SELECT * FROM [qryFarmer Contracts-Forward-Market]

both work?




or, alternatively, does


SELECT [qryFarmer Contracts].Forward_Contract_Num, [qryFarmer
Contracts].Member_Name
FROM [qryFarmer Contracts] INNER JOIN [qryFarmer
Contracts-Forward-Market]
ON
[qryFarmer Contracts].Forward_Contract_Num = [qryFarmer
Contracts-Forward-Market].Rider_Contract_Num;



(I changed the LEFT join for an INNER join) ?



Vanderghast, Access MVP



"JenniferW" <JenniferW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2AD59C8A-6466-4291-BF10-76C796180290@xxxxxxxxxxxxxxxx
What you have below is what I think I already have and it isn't
working.
Here is a SQL statement that I did as a test and it doesn't work
(didn't
want
to give you the actual one since it is very long). It isn't just one
query
that isn't working. All of the queries I have that have left outer
joins
in
them are not working.

SELECT [qryFarmer Contracts].Forward_Contract_Num, [qryFarmer
Contracts].Member_Name
FROM [qryFarmer Contracts] LEFT JOIN [qryFarmer
Contracts-Forward-Market]
ON
[qryFarmer Contracts].Forward_Contract_Num = [qryFarmer
Contracts-Forward-Market].Rider_Contract_Num;

This query is based on 2 other queries.

Thanks for all your help!

"Michel Walsh" wrote:

If your comparison in the ON clause is between the two tables, and not
one
table and one constant, there should be no change. Try:

FROM table1 LEFT JOIN table2 ON table1.field1 = table2.field2


If that does not work, can you post the whole SQL statement?


Vanderghast, Access MVP





"JenniferW" <JenniferW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E0D82833-7150-4CAF-8F83-1F87B4886CED@xxxxxxxxxxxxxxxx
Thanks for the reply. My SQL for the left join was like your first
example.
I did try and change it to what you have in your second example but
I
keep
getting an error "Syntax error on FROM". This is what I changed my
SQL
to:

From table1 LEFT JOIN (SELECT * FROM table2 ON
table1.field1=table2.field2)

So, I thought I had the "(" in the wrong place so I tried this:

From table1 LEFT JOIN (SELECT * FROM table2) ON
table1.field1=table2.field2

and got the error "Syntax error in JOIN operator"

any ideas of what I have wrong?

Thanks!


"Michel Walsh" wrote:

Yes. Well, it happened to some users that I know, and few here on
this
newsgroup, and in their case, it was implying the use of something
like
(JET query):

FROM a LEFT JOIN b
ON (a.f1=b.f2 AND a.f3=cte1 AND b.f4=cte2)


If so, try to change it to:

FROM a LEFT JOIN (SELECT *
FROM b
WHERE f4=cte2) AS newb
ON a.f1=newb.f2
WHERE af.f3=cte1



Note that your case can be different, so that this 'correction' can
be
totally useless for you.


If you don't use constraint implying only the unpreserved table (
like
our
b.f4=cte2 ) in the ON clause, you are not obliged to create the
virtual
table newb.


You may also want to check if you use NOT IN (SELECT ... )
constructions,
where the SELECT can return NULL. That is another thing that seems
to
have
changed, with Jet.





Vanderghast, Access MVP



"JenniferW" <JenniferW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D15940A7-DF48-4115-A0EC-38D916CEB5D3@xxxxxxxxxxxxxxxx
I have a database with an Access frontend and a SQL 2000 backend.
I
moved
the data in the database from Access to SQL 6 months ago and it
has
worked
fine. Then someone went to run a query this week that has a left
outer
join
and all of a sudden it won't work. It gives an error of "ODBC
call
failed".
I at first thought maybe there was some bad data but I have a
copy
of
this
database from March in our SQL 2005 server and it also doesn't
work.
So,
it
doesn't seem like it could be a data problem when this query
worked
fine
last
month with our current data but now won't work with current data
or
data
that
hasn't been updated since March. So, this makes me think there
has
been
some
Microsoft update that has happened in the last month and now is
causing
problems. Has anyone else had a problem with left outer joins
all
of a
sudden not working?

Thanks for any help you can give.











.

Relevant Pages

  • Re: Need help to modify my query
    ... This is not very well-suited to SQL, ... Here's a query that will produce this report - it works ... then uses an outer join, since there may be different numbers ... left outer join S_usergroup as sug ...
    (microsoft.public.sqlserver.programming)
  • Re: Using q() to define a query
    ... I beleive most SQL parsers will ignore white space like that in an SQL query. ... FULL OUTER JOIN jsequipment AS eq ON jsjourneys.equipment = eq.id ... If those were really in the query, I can't imagine the database would run it, so I suppose they're an artifact of the combination of using qto quote my query and using Apache's logger to output it. ...
    (perl.dbi.users)
  • Re: Cartesian product & outer join
    ... An Ambiguous Outer Join basically means that your arrows are pointing the ... My suggestion to most query problems is to not try to do it in just one ... >> Post your SQL. ... >>> time with overtime time categories and multiplies by civilian overtime ...
    (microsoft.public.access.queries)
  • again and again... query analyzer and wrong SQL statement plan... + outer join?
    ... again we have some SQL statement performance problems... ... we have aproblem with a query which use 1 inner join and 3 left outer ... Each outer join is a sub-query which contain a group by: ... This query takes more then 10 minutes, but if I precalculate each sub-query ...
    (microsoft.public.sqlserver.datawarehouse)
  • Left Outer Join: Index Seek not providing all index columns
    ... We have a left outer join query which is intended to prove ... The wrinkle is that the SQL Server 2000 query optimizer is generally ... left outer join entextractitems eei ...
    (microsoft.public.sqlserver.server)