Very Computer

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

HTH

Michael

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

select * from t1, t2, t3
where t1.owner = t2.owner
  and t2.owner = t3.owner
  and t3.owner = 'abcde'
  and t1.filename = t2.filename
  and t2.filename = t3.filename;

Art S. Kagel

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