CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Infeligo
Infeligo

Reputation: 11802

Select a table or array for each row of a bigger select query

Using Oracle 11g

Suppose we have two tables:

CREATE TABLE items (
    item_id NUMBER(22,0)
);

CREATE TABLE messages (
    item_id NUMBER(22,0),
    message_code NUMBER(22,0)
);

where there is one (item) to many (messages) relationship. I want to select all the stuff in a single query to get it into java. Is is possible to write such a query, that it would give me output like:

ID         MESSAGE
----------------------------------
1          (100, 105, 201)
2          (100, 105)

where MESSAGE column is an array or something like that (not a concatenated string)?

Upvotes: 3

Views: 727

Answers (3)

Mike Meyers
Mike Meyers

Reputation: 2895

To build on Quassnoi's answer, there is also the COLLECT function which will do the same thing as MULTISET in the other answer but without the subquery.

CREATE TYPE t_message_code AS TABLE OF INTEGER;

WITH    items AS
        (
        SELECT  1 AS item_id
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id
        FROM    dual
        ),
        messages AS
        (
        SELECT  1 AS item_id, 100 AS message_code
        FROM    dual
        UNION ALL
        SELECT  1 AS item_id, 105 AS message_code
        FROM    dual
        UNION ALL
        SELECT  1 AS item_id, 201 AS message_code
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id, 100 AS message_code
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id, 15 AS message_code
        FROM    dual
        )
SELECT  i.item_id, 
  cast(collect(m.message_code) as t_message_code) as messages_codes
FROM    
  items i
  join messages m on (m.item_id = i.item_id)
group by i.item_id;

Upvotes: 1

zerkms
zerkms

Reputation: 254906

NOTE: this will give you a string (not you wanted), but I'll left this answer here since this question is asked quite often and not all people yet know about such simple solution

  SELECT item_id AS id,
         wm_concat(message_code) AS message
    FROM messages
GROUP BY item_id

If you need parentheses around the message ids list you could use concatenation:

'(' || wm_concat(message_code) || ')' AS message

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425321

You can use field-level cursors:

WITH    items AS
        (
        SELECT  1 AS item_id
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id
        FROM    dual
        ),
        messages AS
        (
        SELECT  1 AS item_id, 100 AS message_code
        FROM    dual
        UNION ALL
        SELECT  1 AS item_id, 105 AS message_code
        FROM    dual
        UNION ALL
        SELECT  1 AS item_id, 201 AS message_code
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id, 100 AS message_code
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id, 15 AS message_code
        FROM    dual
        )
SELECT  item_id,
        CURSOR
        (
        SELECT  message_code
        FROM    messages m
        WHERE   m.item_id = i.item_id
        )
FROM    items i

or create a table type and cast a MULTISET of the values to that type:

CREATE TYPE t_message_code AS TABLE OF INTEGER

WITH    items AS
        (
        SELECT  1 AS item_id
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id
        FROM    dual
        ),
        messages AS
        (
        SELECT  1 AS item_id, 100 AS message_code
        FROM    dual
        UNION ALL
        SELECT  1 AS item_id, 105 AS message_code
        FROM    dual
        UNION ALL
        SELECT  1 AS item_id, 201 AS message_code
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id, 100 AS message_code
        FROM    dual
        UNION ALL
        SELECT  2 AS item_id, 15 AS message_code
        FROM    dual
        )
SELECT  item_id,
        CAST
        (
        MULTISET
        (
        SELECT  message_code
        FROM    messages m
        WHERE   m.item_id = i.item_id
        ) AS t_message_code
        )
FROM    items i

Upvotes: 8

Related Questions