Informix one to many format issue

Question

Trying to fix my Informix query results format from a one to many relationship. My current query is using a JOIN but is creating a new line for every time there is a match to the JOIN ON condition. I should add the below is only an example, the real data is thousands of entries with about a 100 unique "category" entries so I cant hard code WHERE statements, it needs to read each entry and add if a match. I tried a GROUP_CONCAT however is just returned an error, guess its not a informix function, I also tried reading this thread but have yet been unable to get working. Show a one to many relationship as 2 columns - 1 unique row (ID & comma separated list)

Any help will be appreciated.

• IBM/Informix-Connect Version 3.70.UC4
• IBM/Informix LIBGLS LIBRARY Version 5.00.UC5
• IBM Informix Dynamic Server Version 11.70.FC8W1

Tables

movie

name    rating  movie_id
rio g   1   
horton  g   2
blade   r   3
lotr_1  pg13    4
lotr_2  pg13    5
paul_blart  pg  6

category

cat_name    id
kids    1
comedy  2
action  3
fantasy 4

category_member

movie_name  cat_name    catmem_id
lotr_1 action 1
lotr_1 fantasy 2
rio kids 3
rio comedy  4

When I use

#!/bin/bash

echo "SET isolation dirty read;
UNLOAD to /export/home/movie/movieDetail.unl DELIMITER ','
    SELECT a.name, a.rating, b.cat_name
      FROM movie a
      LEFT JOIN category b ON b.movie_name = a.name
;" | dbaccess thedb;

What I get is

rio,g,kids
rio,g,comedy
lotr_1,pg13,action
lotr_1,pg13,fantasy

What I would like is

rio,g,kids,comedy
lotr_1,pg13,action,fantasy

Answer 1

Install the GROUP_CONCAT user-defined aggregate

You must install the GROUP_CONCAT user-defined aggregate from SO 715350 (referenced in your question) into your database. The GROUP_CONCAT aggregate is not defined by Informix, but can be added if you use the SQL from that question. One difference between that and a normal built-in function is that you need to install the aggregate in each database in the server where you need to use it. There might be a way to do a 'global install' (for all databases in a given server), but I've forgotten (or, more accurately, never learned) how to do it.

Writing your queries

With the sample database listed at the bottom:

1. The query in the question does not run:

   SELECT a.name, a.rating, b.cat_name
     FROM movie a
     LEFT JOIN category b ON b.movie_name = a.name;
   SQL -217: Column (movie_name) not found in any table in the query (or SLV is undefined).
   

2. This can be fixed by changing category to category_member. This produces:

   SELECT a.name, a.rating, b.cat_name
     FROM movie a
     LEFT JOIN category_member b ON b.movie_name = a.name;
   rio           g       kids
   rio           g       comedy
   horton        g
   blade         r
   lotr_1        pg13    action
   lotr_1        pg13    fantasy
   lotr_2        pg13
   paul_blart    pg
   

3. The LEFT JOIN appears to be unwanted. And using GROUP_CONCAT produces approximately the desired answer:

   SELECT a.name, a.rating, GROUP_CONCAT(b.cat_name)
     FROM movie a
     JOIN category_member b ON b.movie_name = a.name
    GROUP BY a.name, a.rating;
   rio          g       kids,comedy
   lotr_1       pg13    action,fantasy
   

4. If you specify the delimiter as ,, the commas in the data from the GROUP_CONCAT operator will be escaped to avoid ambiguity:

   SELECT a.NAME, a.rating, GROUP_CONCAT(b.cat_name)
     FROM movie a
     JOIN category_member b ON b.movie_name = a.NAME
    GROUP BY a.NAME, a.rating;
   rio,g,kids\,comedy
   lotr_1,pg13,action\,fantasy
   

   Within standard Informix utilities, there isn't a way to avoid that; they don't leave the selected/unloaded data in an ambiguous format.

5. I'm not convinced that the database schema is very well organized. The Movie table is OK; the Category table is OK; but the Category_Member table would be more orthodox if it used the schema:

   DROP TABLE IF EXISTS category_member;
   CREATE TABLE category_member
   (
       movie_id INTEGER NOT NULL REFERENCES Movie(Movie_id),
       category_id INTEGER NOT NULL REFERENCES Category(Id),
       PRIMARY KEY(movie_id, category_id)
   );
   INSERT INTO category_member VALUES(4, 3);
   INSERT INTO category_member VALUES(4, 4);
   INSERT INTO category_member VALUES(1, 1);
   INSERT INTO category_member VALUES(1, 2);
   
   -- Use GROUP_CONCAT
   SELECT a.NAME, a.rating, GROUP_CONCAT(c.cat_name)
     FROM movie a
     JOIN category_member b ON b.movie_id = a.movie_id
     JOIN category c ON b.category_id = c.id
    GROUP BY a.NAME, a.rating;
   

   The output from this query is the same as from the previous one, but the joining is more orthodox.

Sample database

DROP TABLE IF EXISTS movie;
CREATE TABLE movie
(
    name VARCHAR(20) NOT NULL UNIQUE,
    rating CHAR(4) NOT NULL,
    movie_id SERIAL NOT NULL PRIMARY KEY
);
INSERT INTO movie VALUES("rio", "g", 1);
INSERT INTO movie VALUES("horton", "g", 2);
INSERT INTO movie VALUES("blade", "r", 3);
INSERT INTO movie VALUES("lotr_1", "pg13", 4);
INSERT INTO movie VALUES("lotr_2", "pg13", 5);
INSERT INTO movie VALUES("paul_blart", "pg", 6);

DROP TABLE IF EXISTS category;
CREATE TABLE category
(
    cat_name VARCHAR(10) NOT NULL UNIQUE,
    id SERIAL NOT NULL PRIMARY KEY
);
INSERT INTO category VALUES("kids", 1);
INSERT INTO category VALUES("comedy", 2);
INSERT INTO category VALUES("action", 3);
INSERT INTO category VALUES("fantasy", 4);

DROP TABLE IF EXISTS category_member;
CREATE TABLE category_member
(
    movie_name VARCHAR(20) NOT NULL,
    cat_name VARCHAR(10) NOT NULL,
    catmem_id SERIAL NOT NULL PRIMARY KEY
);
INSERT INTO category_member VALUES("lotr_1", "action", 1);
INSERT INTO category_member VALUES("lotr_1", "fantasy", 2);
INSERT INTO category_member VALUES("rio", "kids", 3);
INSERT INTO category_member VALUES("rio", "comedy", 4);