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