Newbie question about SQL syntax for joins

Newbie question about SQL syntax for joins

Post by Len Wolfenstei » Sun, 23 Nov 1997 04:00:00



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

 
 
 

Newbie question about SQL syntax for joins

Post by Noodl » Mon, 24 Nov 1997 04:00:00


Len,

From the added search criteria in the where clause, ( city.name  = 'WARSAW'),
my initial gut reaction is that your SQL is fine; it just needs to be tuned.
Have you tried running your SQL through EXPLAIN and TKPROF ?  Also, what
optimizer mode is your database running in ?  

If you haven't analyzed your indexes to get good stats for the optimizer to use
( assuming you're not using rule-based optimization), then obviously the
optimizer is making some very bad decisions on how to parse and execute your
SQL.

You might want to look into the documentation regarding SQL Hints to give
Oracle some hints on how to run your SQL.  Remember, only you know your data
frequency, cardinality, etc.

For Example,  sometimes just switching the table order can change which table
is used for driving the where clause parameters. In the past I've moved the
table order and the where clause parameter order around with the SQL Timer
feature on and you'd be surprized what different performance effects you get.

' hope I've given you some ideas.

Cliff

 
 
 

Newbie question about SQL syntax for joins

Post by Matthias Gre » Tue, 25 Nov 1997 04:00:00



> 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


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    :-)

 
 
 

1. Newbie - Easy (I think) SQL Syntax Question, 3 table join

I have three identical tables, t1, t2, t3

Each table contains a complete list of files on a particular server.

One of the fields in each table is owner (the owner of an individual
file)

What is the SQL syntax to get a list of all the files owned by a
single user "abcde" from the three tables ?

I can do this in three statements:

Select * from t1 where t1.owner = "abcde";
Select * from t2 where t2.owner = "abcde";
Select * from t3 where t3.owner = "abcde";

The question is how can I do this in one statement?

Here is my first attempt:

Select * from t1,t2,t3
where t1.owner = "abcde"
and t1.owner = t2.owner
and t2.owner = t3.owner

This fails to give the correct answer.

select * from (t1 INNER JOIN t2 on t1.owner = t2.owner) INNER JOIN t3
on t3.owner = t1.owner

This also fails to give the correct answer.

Thanks,

John

2. dw software spec

3. Newbie SQL Syntax Question

4. ODBC Timeout Expired in DLL

5. newbie sql syntax question.

6. Error during Runtime Install on Win95B - Please Help

7. Newbie SQL stored proc syntax question

8. View with an aggregate

9. Newbie question: SQL syntax

10. newbie - INNER JOIN syntax error

11. newbie: SQL join/union question

12. Update a table using a join??? (Syntax Question)

13. Question about JOIN syntax.