Bugs item #1979819, was opened at 2008-05-30 22:06 Message generated for change (Comment added) made by nielsnes You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1979819&group_id=56967
Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: SQL/Core Group: MonetDB5 CVS Head >Status: Closed >Resolution: Works For Me Priority: 5 Private: No Submitted By: Stefan de Konink (skinkie) Assigned to: Niels Nes (nielsnes) Summary: LEFT JOIN combined with same table Initial Comment: I have three tables: Usernames, Relations, Relation_tags First; I want to lookup all Relations having Relation_tags with k='type' and v='way' After this I want to do a left join, on relation_tags getting the other values of Relation_tags with that are semantically linked to Relations; select relations.id, relations.visible, usernames.username, '2007-09-07T16:39:10+01:00' FROM relations, usernames, relation_tags AS constr WHERE relations.username = usernames.id AND constr.k='type' AND constr.v='way'; Works. Adding the LEFT JOIN; select relations.id, relations.visible, usernames.username, '2007-09-07T16:39:10+01:00' FROM relations, usernames, relation_tags AS constr LEFT JOIN relation_tags AS joiner ON relations.id = joiner.relation WHERE relations.username = usernames.id AND constr.k='type' AND constr.v='way'; !SELECT: no such column 'relations.id' No such column? Isn't that strange? ---------------------------------------------------------------------- >Comment By: Niels Nes (nielsnes) Date: 2008-06-02 06:41 Message: Logged In: YES user_id=43556 Originator: NO MonetDB is correctly complaining. The query is incorrect. ---------------------------------------------------------------------- Comment By: Niels Nes (nielsnes) Date: 2008-05-31 18:56 Message: Logged In: YES user_id=43556 Originator: NO please add the create statements for you 3 tables. ---------------------------------------------------------------------- Comment By: Stefan de Konink (skinkie) Date: 2008-05-31 01:54 Message: Logged In: YES user_id=533026 Originator: YES I see the leftjoinbug.sql example results in 'NULL's like my own queries with LEFT join. Maybe something changed lately, but not so long ago this all worked. sql>SELECT id, X(g), Y(g), visible, usernames.username, '2007-09-07T16:39:10+01:00', node_tags.k, node_tags.v FROM nodes, usernames LEFT JOIN node_tags ON id = node WHERE id=44803343 LIMIT 2; +----------+-----------------------+-----------------------+--------+---------+--------------------------+--+--+ | id |x_g |y_g |visible |username |single_value |k |v | +==========+=======================+=======================+========+=========+==========================+==+==+ | 44803343 | 52.001168999999997 | 4.3722767999999999 |true |unknown |2007-09-07T16:39:10+01:00 |n |n | : | | | | | |u |u | : | | | | | |l |l | : | | | | | |l |l | +----------+-----------------------+-----------------------+--------+---------+--------------------------+--+--+ sql>SELECT id, X(g), Y(g), visible, usernames.username, '2007-09-07T16:39:10+01:00', node_tags.k, node_tags.v FROM nodes, usernames, node_tags WHERE id = node AND id=44803343 LIMIT 2; +----------+-----------------------+-----------------------+--------+---------+--------------------------+---------+------+ | id |x_g |y_g |visible |username |single_value |k |v | +==========+=======================+=======================+========+=========+==========================+=========+======+ | 44803343 | 52.001168999999997 | 4.3722767999999999 |true |unknown |2007-09-07T16:39:10+01:00 |'source' |'AND' | +----------+-----------------------+-----------------------+--------+---------+--------------------------+---------+------+ ---------------------------------------------------------------------- Comment By: Stefan de Konink (skinkie) Date: 2008-05-30 22:08 Message: Logged In: YES user_id=533026 Originator: YES sql>select relations.id, relations.visible, usernames.username, '2007-09-07T16:39:10+01:00' FROM relations, usernames, relation_tags AS tags WHERE relations.username = usernames.id AND tags.k='type' AND tags.v='way'; sql>select relations.id, relations.visible, usernames.username, '2007-09-07T16:39:10+01:00' FROM relations, usernames, relation_tags AS constr WHERE relations.username = usernames.id AND constr.k='type' AND constr.v='way'; sql>select relations.id, relations.visible, usernames.username, '2007-09-07T16:39:10+01:00' FROM relations, usernames, relation_tags AS constr LEFT JOIN relation_tags AS joiner ON relations.id = joiner.relation WHERE relations.username = usernames.id AND constr.k='type' AND constr.v='way'; !SELECT: no such column 'relations.id' sql>select relations.id, relations.visible, usernames.username, '2007-09-07T16:39:10+01:00' FROM relations, usernames LEFT JOIN relation_tags AS joiner ON relations.id = joiner.relation WHERE relations.username = usernames.id; !SELECT: no such column 'relations.id' sql>select relations.id, relations.visible, usernames.username, '2007-09-07T16:39:10+01:00' FROM relations, usernames LEFT JOIN relation_tags AS joiner ON id = joiner.relation WHERE relations.username = usernames.id; sql>select relations.id, relations.visible, usernames.username, '2007-09-07T16:39:10+01:00' FROM relations, usernames, relation_tags AS constr LEFT JOIN relation_tags AS joiner ON id = joiner.relation WHERE relations.username = usernames.id AND constr.k='type' AND constr.v='way'; !SELECT: identifier 'id' unknown ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=1979819&group_id=56967 ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Monetdb-bugs mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
