Re: convert query from mysql

On 4 Jan 2014 13:09, "Jaime Casanova" <jaime(at)2ndquadrant(dot)com> wrote:
>
> On Tue, Dec 17, 2013 at 3:44 PM, Mike Andrewjeski
> <Mike(dot)Andrewjeski(at)businesswire(dot)com> wrote:
> >
> > Hi all,
> >
> > psql newbie here. I'm working to convert a mysql query written in ruby
to
> > using postgres as our database is now postgres 9.2
> >
> > Here's the mysql query:
> >
> > select nodes.name, GROUP_CONCAT(node_groups.name) from node_groups,
> > node_group_memberships, nodes where nodes.id =
> > node_group_memberships.node_id and node_groups.id =
> > node_group_memberships.node_group_id and nodes.name IN (SELECT name
> > from nodes) group by nodes.name order by nodes.name")
> > What I'm having an issue with is the IN Clause. Not sure how to do
that in
> > psql.
> >
>
> Hi,
>
> I was looking at this and i admit i don't understand how that IN could
> ever be false (which could be caused by the beer i just drank).

Lets not blame beer :-)

> The reason i don't understand it is that you are checking if the names
> in table nodes exists in the table nodes :S

Agree! It unneccessarily increases the cost of query.
I am not well versed with My SQL. But if it supports SQL properly you would
not need that IN. But if that IN was added cause of some 'syntax/planner
defficiency' in MySQl, you can safely drop it in PostgreSQL.
BTW what is the issue you are having in PostgreSQL? Error or unexpected
result?