MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Diana Soares Date:January 15 2003 7:32pm
Subject:RE: three table join
View as plain text  
Try adding a "T2.PK IS NOT NULL" or "T2.FK1 IS NOT NULL":

SELECT SomeStuff
FROM Table1 AS T1
LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1
RIGHT JOIN Table3 AS T3 ON T2.FK2 = T3.PK
WHERE T1.PK=999 AND T2.FK1 IS NOT NULL


Example with old tables i have:

mysql> select * from t1;
+----+----+
| id | v  |
+----+----+
|  1 | 23 |
|  2 | 18 |
|  3 |  6 |
+----+----+
3 rows in set (0.00 sec)

mysql> select * from t2;
+----+-------+-------+----+
| id | id_t1 | id_t3 | v  |
+----+-------+-------+----+
|  1 |     0 |     0 |  2 |
|  2 |     1 |     1 | 89 |
|  3 |     2 |     3 |  8 |
+----+-------+-------+----+
3 rows in set (0.00 sec)

mysql> select * from t3;
+----+----+----+
| id | v1 | v2 |
+----+----+----+
|  1 |  8 |  6 |
|  2 | 28 | 12 |
|  3 | 56 | 23 |
|  4 |  2 | 34 |
+----+----+----+
4 rows in set (0.00 sec)

mysql> select * from t1 as T1 LEFT JOIN t2 AS T2 ON (T1.id = T2.id_t1)
RIGHT JOIN t3 AS T3 ON (T2.id_t3 = T3.id) WHERE T1.id=2 AND T2.id IS NOT
NULL;
+----+----+------+-------+-------+------+----+----+----+
| id | v  | id   | id_t1 | id_t3 | v    | id | v1 | v2 |
+----+----+------+-------+-------+------+----+----+----+
|  2 | 18 |    3 |     2 |     3 |    8 |  3 | 56 | 23 |
+----+----+------+-------+-------+------+----+----+----+
1 row in set (0.00 sec)



On Wed, 2003-01-15 at 16:53, Tab Alleman wrote:
> Nice that this came up when it did.. I'm currently struggling with a
> three-table join.
> 
> Table1.PK = Table2.FK1
> Table3.PK = Table2.FK2
> 
> My last effort looks something like:
> 
> SELECT SomeStuff
> FROM Table1 AS T1
> LEFT JOIN Table2 AS T2 ON T1.PK = T2.FK1
> RIGHT JOIN Table3 AS T3 ON T2.FK2 = T3.PK
> WHERE T1.PK=999
> 
> I want it to return 1 row, but it's returning as many rows as there are
> in Table3.  Where am I goofing?
> 
> TIA, 
> Tab
> mysql
> 
> -----Original Message-----
> From: Michael T. Babcock [mailto:mbabcock@stripped] 
> Sent: Wednesday, January 15, 2003 8:30 AM
> To: Josh L Bernardini
> Cc: mysql@stripped
> Subject: Re: three table join
> 
> 
> I've wanted to post this query example a few times (and I hope I got it 
> right; mornings aren't my best time) ... multiple JOINs:
> 
>      SELECT stuff
>        FROM table1
>   LEFT JOIN table2
>          ON table1.fk = table2.pk
>   LEFT JOIN table3
>          ON table2.fk = table3.pk
>       WHERE other_conditions
>         ...
> 
> You can repeat that as many levels as you want (performance depends on 
> indexing and the optimizer).  You need to think in terms of what would 
> be equal to what between tables in the correct result row.  So if you 
> would do a secondary sub-select of "SELECT fk from table2 where ..." 
> then you end up with a left join like above.
> 
-- 
Diana Soares

Thread
three table joinJosh L Bernardini15 Jan
  • Re: three table joinMichael T. Babcock15 Jan
  • Re: three table joinBrent Baisley15 Jan
RE: three table joinTab Alleman15 Jan
  • Re: three table joinHarald Fuchs16 Jan
  • Re: three table joinMichael T. Babcock22 Jan
RE: three table joinDiana Soares15 Jan