Re: Newbie question about SQL syntax for joins

Len Wolfenstein wrote:
>
> Hi. I am trying to select fields from two tables that I join a field
> I'll call ID. What happens is that the selection statement works fine
> when I am applying criteria against fields in one table, but when I
> apply criteria against fields in both tables, I don't get a result set -
> instead my query is just suspended, waiting for the result set forever.
> I do not get any errors.
>
> For example:
>
> This one would work just fine:
> select table1.id, table1.name, table1.balance, table2.id, table2.city,
> table2.state from table1, table2 where table1.id = table2.id and
> table1.balance > 1000 and table1.name = "Premier";
>
> But this one would not:
> select table1.id, table1.name, table1.balance, table2.id, table2.city,
> table2.state from table1, table2 where table1.id = table2.id and
> table1.balance > 1000 and table1.name = "Premier" and table2.city =
> "Warsaw";
>
> My thought is that I need to apply the table2 criteria in a subquery
> that would form the recordsource for the join - maybe something like:
>
> select table1.id, table1.name, table1.balance, table2.id, table2.city,
> table2.state from table1, (select table2.* from table2 where table2.city
> = "Warsaw") where table1.id = table2.id and table1.balance > 1000 and
> table1.name = "Premier";
>
> The actual statements are much longer and more complex than these, but I
> think I've shown the relevant factors.
>
> Any help at all would be greatly appreciated, as this thing is genuinely
> driving me crazy.
>
> Thanks in advance,
> Len Wolfenstein
> lawolfe_at_msg.pacbell.com

Hi,

if your statement is much more complex then the about check if you didn't forget to join a third or fourth table mentioned in your from-clause. This will cause a cartessian product taking up immense time depending on the size of your table.

-- 
Regards

Matthias Gresz    :-)