On Wed, 15 Jan 2003, Joseph Shraibman wrote: > select distinct n.thekey, n.val, t.txt FROM num_tab n LEFT JOIN txt_tab t ON >n.thekey = > t.thekey > WHERE n.thekey < 5 AND t.class = class_tab.tkey AND n.class = class_tab.class; > produces:
Note that the above uses the non-standard postgres behavior of adding from clauses, it's not technically valid SQL. > thekey | val | txt > --------+-----+------ > 2 | 2 | two > 4 | 4 | four > ... which is not what we want, because 1,3, and 5 aren't included, but: > > select distinct n.thekey, n.val, t.txt FROM num_tab n LEFT JOIN txt_tab t ON >n.thekey = > t.thekey AND t.class = class_tab.tkey AND n.class = class_tab.class > WHERE n.thekey < 5; > produces: > NOTICE: Adding missing FROM-clause entry for table "class_tab" > ERROR: JOIN/ON clause refers to "class_tab", which is not part of JOIN > > So how do I do this? I think you want something like: select distinct n.thekey, n.val, t.txt FROM class_tab JOIN num_tab n using (class) LEFT JOIN txt_tab t on (t.thekey=n.thekey and t.class = class_tab.tkey); ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
