Tim

It seems that the sequence in which the joins are listed in the query is now important. Shouldn't ElevateDB be able to figure this out and rearrange if necessary?

SELECT
"_DB_Contacts"."_ID"
FROM
"Contacts" AS  "_DB_Contacts"

JOIN "Companies" AS "_DB_Companies" ON _DB_Career."_fkCompanies" = "_DB_Companies"."_ID"
JOIN "Career" AS "_DB_Career" ON "_DB_Contacts"."_ID" = "_DB_Career"."_fkContacts"
JOIN "Sites" AS "_DB_Sites" ON _DB_Sites."_SiteID" = "_DB_Career"."_fkSites"
WHERE
"_DB_Companies"."_Name" LIKE  '%moto%'


ElevateDB Error #700 An error was found in the statement at line 6 and column 40 (Invalid expression "_DB_Career"."_fkCompanies" found, this expression refers to a table or column that is not in scope yet)


I can get the query to do something (not sure if its right, but its not what I want) by moving the joins around

================================================================================
SQL Query (Executed by ElevateDB 1.09 Build 3)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

SELECT ALL
"_DB_Contacts"."_ID" AS "_ID"
FROM "Contacts" AS "_DB_Contacts" INNER JOIN "Career" AS "_DB_Career" ON
"_DB_Contacts"."_ID" = "_DB_Career"."_fkContacts"
INNER JOIN "Sites" AS "_DB_Sites" ON "_DB_Sites"."_SiteID" =
"_DB_Career"."_fkSites",
INNER JOIN "Companies" AS "_DB_Companies" ON "_DB_Career"."_fkCompanies" =
"_DB_Companies"."_ID"
WHERE "_DB_Companies"."_Name" LIKE '%moto%'

Source Tables
-------------

Contacts (_DB_Contacts): 9310 rows
Career (_DB_Career): 9502 rows
Sites (_DB_Sites): 9318 rows
Companies (_DB_Companies): 9303 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the Companies table:

"_DB_Companies"."_Name" LIKE '%moto%' [Index scan: 9303 keys, 1155072 bytes
estimated cost]

Joins
-----

The driver table was the Contacts (_DB_Contacts) table

The Contacts (_DB_Contacts) table was joined to the Career (_DB_Career) table
with the inner join expression:

"_DB_Contacts"."_ID" = "_DB_Career"."_fkContacts"

The Career (_DB_Career) table was joined to the Sites (_DB_Sites) table with the
inner join expression:

"_DB_Sites"."_SiteID" = "_DB_Career"."_fkSites"

The Career (_DB_Career) table was joined to the Companies (_DB_Companies) table
with the inner join expression:

"_DB_Career"."_fkCompanies" = "_DB_Companies"."_ID"

The optimizer attempted to re-order the joins to a more optimal order

The optimizer successfully re-ordered the joins into this more optimal order:

The driver table was the Companies (_DB_Companies) table

The Companies (_DB_Companies) table was joined to the Career (_DB_Career) table
with the inner join expression:

"_DB_Career"."_fkCompanies" = "_DB_Companies"."_ID"

The Career (_DB_Career) table was joined to the Contacts (_DB_Contacts) table
with the inner join expression:

"_DB_Contacts"."_ID" = "_DB_Career"."_fkContacts"

The Career (_DB_Career) table was joined to the Sites (_DB_Sites) table with the
inner join expression:

"_DB_Sites"."_SiteID" = "_DB_Career"."_fkSites"

The following join condition was applied to the Career (_DB_Career) table:

"_DB_Career"."_fkCompanies" = "_DB_Companies"."_ID" [Index scan]

The following join condition was applied to the Contacts (_DB_Contacts) table:

"_DB_Contacts"."_ID" = "_DB_Career"."_fkContacts" [Index scan]

The following join condition was applied to the Sites (_DB_Sites) table:

"_DB_Sites"."_SiteID" = "_DB_Career"."_fkSites" [Row scan]

================================================================================
706984 row(s) returned in 12.137 secs
================================================================================

Roy Lambert

The VAST amount of rows was my fault


SELECT
"_DB_Contacts"."_ID"
FROM
"Contacts" AS  "_DB_Contacts"
JOIN "Career" AS "_DB_Career" ON "_DB_Contacts"."_ID" = "_DB_Career"."_fkContacts"
JOIN "Companies" AS "_DB_Companies" ON _DB_Career."_fkCompanies" = "_DB_Companies"."_ID"
JOIN "Sites" AS "_DB_Sites" ON _DB_Sites."_SiteID" = "_DB_Career"."_fkSites" AND _DB_Sites._fkCompanies = _DB_Companies._ID
WHERE
"_DB_Companies"."_Name" LIKE  '%moto%'

cuts it down to 73 which is about what I would have expected, but having to get the joins in the right order could be a showstopper for generating the JOINs from fields in the WHERE clause which is essentially what I'm doing.

Roy Lambert

Roy,

<< It seems that the sequence in which the joins are listed in the query is
now important. Shouldn't ElevateDB be able to figure this out and rearrange
if necessary? >>

Before any joins are rearranged, they have to be semantically correct and be
able to be executed "as-is".

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< cuts it down to 73 which is about what I would have expected, but having
to get the joins in the right order could be a showstopper for generating
the JOINs from fields in the WHERE clause which is essentially what I'm
doing. >>

Why are you generating the joins from the WHERE clause ?  Are these existing
queries, or queries from an interactive query builder ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Why are you generating the joins from the WHERE clause ? Are these existing
>queries, or queries from an interactive query builder ?

interactive query builder

Its not really the WHERE clause its a list of fields, tests and criteria which will become the WHERE clause, it just seemed easier to say the WHERE clause.

Roy Lambert

Tim

><< It seems that the sequence in which the joins are listed in the query is
>now important. Shouldn't ElevateDB be able to figure this out and rearrange
>if necessary? >>
>
>Before any joins are rearranged, they have to be semantically correct and be
>able to be executed "as-is".

Please translate. Are you saying they do have to be in the "correct" order or what?

What it looks like to me is that I have to have the joins in the following sequence

Career->Contacts
Companies->Career
Sites->Career (should have been Sites->Career AND ->Companies to get the right result)

for anything to work. Is that right?

Roy Lambert

Roy,

<< Please translate. Are you saying they do have to be in the "correct"
order or what? >>

A join cannot reference a table that has not been included in the FROM
clause yet, either by itself or via a JOIN clause.  You have to respect the
left-to-right parsing of the SQL statement.

<< What it looks like to me is that I have to have the joins in the
following sequence >>

They can be in any sequence that you want, provided that they obey the above
rule.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< Please translate. Are you saying they do have to be in the "correct"
>order or what? >>
>
>A join cannot reference a table that has not been included in the FROM
>clause yet, either by itself or via a JOIN clause. You have to respect the
>left-to-right parsing of the SQL statement.

Further translation - yes they do have to be in the right sequence

><< What it looks like to me is that I have to have the joins in the
>following sequence >>
>
>They can be in any sequence that you want, provided that they obey the above
>rule.

I love this one - I convert this to "they can be in any sequence you want as long as its the right one" <vbg>

So I need a bit of a rewrite of my query generator then.

Roy Lambert

Roy,

<< I love this one - I convert this to "they can be in any sequence you want
as long as its the right one" <vbg> >>

Well, what I mean is this:

Say you have a table called Table1 and 3 other tables (Table2, etc...).  If
Table1 is the first referenced table, then it any join after Table1 that
references Table1 can be in any order that you want, it just has to come
*after* Table1 is introduced into scope.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Say you have a table called Table1 and 3 other tables (Table2, etc...).
If Table1 is the first referenced table, then it any join after Table1 that
references Table1 can be in any order that you want, it just has to come
*after* Table1 is introduced into scope. >>

I haven't explicitly looked it up in the standard documents, but I do think
that any table referenced in the FROM clause (which includes joined tables)
is implicitly in scope during the processing of the clause. I'm not
absolutely sure, but It might be an issue with the EDB implementation here.

Ole Willy Tuv