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

John Walsh wrote:

>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";
>
>
select * from t1 where t1.owner = "abcde"
union
select * from t2 where t2.owner = "abcde"
union
select * from t3 where t3.owner = "abcde";

HTH

Michael

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

On Thu, 10 Jul 2003 18:30:32 -0400, John Walsh wrote:

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

> 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