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

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

Post by John Wal » Sat, 12 Jul 2003 07:30:32



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

 
 
 

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

Post by Michael Krzepkowsk » Sat, 12 Jul 2003 07:51:17



>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

- Show quoted text -

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


 
 
 

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

Post by Art S. Kage » Sat, 12 Jul 2003 08:03:57


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

 
 
 

1. easy (I think) dbase/FoxPro question (about JOIN)

        OK.  I have two databases.  They are linked by a one-to-many
mapping.  That is, Database A has a primary key.  For each entry in
Database A, there are 0, 1, or many entries in Database B which share the
primary key.

        If I join the two databases, I get Database B.  (There are no naked
entries in Database B which do not join to A).  On the other hand, there
are many entries in Database A with no corresponding entries in Database B.

        How can I generate a list of all entries in Database A with NO
corresponding entries in Database B?

        I hope this question sounds clearer to you than it does to me.  My
apologies for my lack of facility with database terms.

        Replies by E-mail, please, as I expect the answer to this to be
trivial.

-- David.

2. Detecting file existance

3. Newbie question about SQL syntax for joins

4. 7.3.4.4 patch with NT SP5

5. NEWBIE: easy syntax question with UPDATE statement

6. assigning values to arrays from query

7. Easy (I think) SQL Statement question

8. Conference Announcement and Preliminary Call for Papers (Very Large DB)

9. Easy SQL*Plus question (I think)

10. Simple SQL syntax question (I think)

11. Easy SQL Syntax Question

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