Thanks Berenger. So if I had three related tables would I do this?

SELECT Persistent.oid, name FROM
Persistent JOIN NamedPersistent on NamedPersistent.oid=Persistent.oid,
NamedAndDatedPersistent JOIN NamedPersistent on
NamedPersistent.oid=NamedAndDatedPersistent.oid
WHERE name="foo"

?

TIA,

R.


-----Original Message-----
From: Berenger Enselme [mailto:BEnselme@...]
Sent: Monday, March 31, 2003 11:27 AM
To: 'ib-support@yahoogroups.com'
Subject: RE: [ib-support] Natural Joins and SELECT queries

> -----Original Message-----
> From: Robert DiFalco [mailto:rdifalco@...]
> Sent: Monday, March 31, 2003 2:11 PM
> To: ib-support@yahoogroups.com
> Subject: [ib-support] Natural Joins and SELECT queries
>
>
> Say I have the following two tables:
>
> create table Persistent
> (
> oid INTEGER NOT NULL,
> typeId INTEGER NOT NULL,
> PRIMARY KEY ( oid )
> );
>
> create table NamedPersistent
> {
> oid INTEGER NOT NULL,
> name VARCHAR(64)
> PRIMARY KEY ( oid )
> );
>
> Now, let's assume I have 10 related records in both. What is the best
> way to performed joined queries on these two tables. I notice that the
> following doesn't work.
>
> select Persistent.oid, name
> from Persistent, NamedPersistent
> where name="foo";
>
> If I have 10 records, I get something like 100 results back.
> Anyone have
> any ideas?

Perfectly SQL-compliant behaviour !

> But this makes me realize I can do it any number of ways. Can someone
> explain the difference between these variants?
>
> select Persistent.oid, name
> from Persistent, NamedPersistent
> where Persistent.oid=NamedPersistent.oid and name="foo";
>
> select Persistent.oid, name
> from Persistent, NamedPersistent
> where Persistent.oid=NamedPersistent.oid and name="foo";
>
> select NamedPersistent.oid, name
> from Persistent, NamedPersistent
> where NamedPersistent.oid=Persistent.oid and name="foo";
>
> select Persistent.oid, name
> from Persistent, NamedPersistent
> where NamedPersistent.oid=Persistent.oid and name="foo";

They're all the same as far as I understand.

> Is there a better way to do these types of queries?

Yes... using an explicit JOIN clause:

SELECT Persistent.oid, name FROM
Persistent JOIN NamedPersistent on NamedPersistent.oid=Persistent.oid
WHERE name="foo"

Berenger





Yahoo! Groups Sponsor
ADVERTISEMENT




To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.