Re: convert query from mysql

Mike Andrewjeski <Mike(dot)Andrewjeski(at)businesswire(dot)com> wrote:

> 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.

That query looks fine as it is if you just replace this:

  GROUP_CONCAT(node_groups.name)

with this:

  string_agg(node_groups.name, ',')

If that doesn't work, please show a test case where you provide SQL
code to create and populate the tables with minimal columns and
rows.  Show your query, explain what you think the results should
be, and show what you are getting instead.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company