Moritz Onken wrote: > > Am 29.05.2009 um 19:04 schrieb Андрей Костенко: > >> Not so easy. If DBMS can't find records in joined table, it returns >> NULLs: >> test=# SELECT * FROM table1 LEFT JOIN table2 ON >> table2.table1_id=table1.id AND table2.user_id=123; >> id | table1_id | user_id >> ----+-----------+--------- >> 1 | 1 | 123 >> 2 | | >> 3 | 3 | 123 >> 4 | | >> 5 | | >> (5 rows) >> >> but your code will return only: >> >> test=# SELECT * FROM table1 LEFT JOIN table2 ON >> table2.table1_id=table1.id WHERE table2.user_id=123; >> id | table1_id | user_id >> ----+-----------+--------- >> 1 | 1 | 123 >> 3 | 3 | 123 >> (2 rows) > > > does > >> SELECT * FROM table1 LEFT JOIN table2 ON table2.table1_id=table1.id >> WHERE table2.user_id=123 OR table2.user_id IS NULL; >
No it doesn't. Remember - where operates on the resulting "virtual" table after all the joins are resolved. So you do your left join and get back ALL rows from table 1 and ALL related rows from table2 (or you get NULLs). Now you apply the where filter and you get back _only_ the table1 rows that have a related row with user_id=123, or ones that have no related rows at all. All table1 rows which have related rows that are _different_ than user_id=123 will be thrown away. This is the infamous "left join with right condition" problem, and there is no workaround for it, except by specifying the condition in the join itself. _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
