On Thu, Jul 27, 2006 at 07:30:01PM +0000, Steven Adams wrote: > select ia.name, iac.internal > from information_assets as ia > left outer join information_asset_categories as iac on(ia.category_id = > iac.id) > and ia.id = 21 > > This causes all rows in information_assets to be returned despite the "and" > clause. Adding "join information_assets as ia2 on(ia.id = ia2.id)" after > the outer join corrects this.
http://www.postgresql.org/docs/8.1/interactive/queries-table-expressions.html#QUERIES-JOIN LEFT OUTER JOIN First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Thus, the joined table unconditionally has at least one row for each row in T1. The "and ia.id = 21" expression is part of the outer join condition that restricts rows from information_asset_categories (T2); it doesn't restrict rows from information_assets (T1). If you don't want all rows from information_assets then why are you using an outer join? -- Michael Fuhr ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
