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:[EMAIL PROTECTED]] > Sent: Wednesday, January 15, 2003 8:30 AM > To: Josh L Bernardini > Cc: [EMAIL PROTECTED] > 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 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
