Nested SQL query or subquery

Question

I created the following query on the three existing tables, which also works fine.

Organization Table

-------------
id    name        service
--------------------------------
1     CEO            1, 2, 4
2     ALF            1, 3, 5
3     FRGT           
4     OFFICE_IT      1, 2, 7

City Table

-------------
id    name
-------------
10     Berlin
20     Paris
30     London
40     Rom

Relation Table

-------------
oid    cid
-------------
1       10
2       20
3       30
4       40

SELECT o.name as Organization, o.service as service,  c.name as City 
FROM Organization o
    INNER JOIN Relation rel ON o.id = rel.oid
    INNER JOIN City c ON rel.cid  = c.id 
WHERE o.service IS NOT NULL

New View:

 id    Organization    service      City
-----------------------------------------------
1     CEO              1, 2, 4      Berlin
2     ALF              1, 3, 5      Paris
4     OFFICE_IT        1, 7         Rom

But now I would like to design the table in such a way that the values of the string in the "service" column are written separately in one line.

Please see the example:

id    Organization    service      City
-----------------------------------------------
1     CEO              1            Berlin
1     CEO              2            Berlin
1     CEO              4            Berlin
2     ALF              1            Paris
2     ALF              3            Paris
2     ALF              5            Paris
4     OFFICE_IT        1            Rom
4     OFFICE_IT        7            Rom

In the meantime I managed to get a corresponding query for the "Organization" table only, without Relation to city.

SELECT O.ID
     , O.name
     , trim(COLUMN_VALUE) AS service
  FROM Organization o ,
    xmltable(('"'
    || REPLACE(o.service, ',', '","')
    || '"'))

However, I currently lack the idea of squeezing both individual queries into a single SQL query. Do you have a solution?

Please do not post images of tables (especially when you have already posted the tables as text). — MT0, Mar 17 '23 at 12:25
If you are just asking how to combine your two queries then use either `CROSS APPY` or `CROSS JOIN` for the `XMLTable` or, if you want to use a legacy comma-join then put the comma-join last. [fiddle](https://dbfiddle.uk/xpDo54OR). — MT0, Mar 17 '23 at 12:39
Yes, I am primarily concerned with how I can combine both queries. Or how do I simplify both query's into a single query. Ah, I just saw that you gave a couple of possible solutions via "fiddle". Thanks, I'll take a closer look at these, especially what "CROSS Apply" and "CROSS JOIN" does. — Sprössling, Mar 17 '23 at 12:55
`FROM x CROSS JOIN y` is the ANSI standard syntax. `FROM x, y` is the proprietary Oracle syntax for the same thing (you can mix the two syntaxes but the ANSI joins need to be before the comma joins). `FROM x CROSS APPLY y` is syntax available from Oracle 12 and is used when `y` is dependent on `x` (in your case, `y` would be the `XMLTABLE` derived from `o.service`). They would all work equally for your case (as shown by the fiddle in the previous comment) — MT0, Mar 17 '23 at 13:07
@MT0, I applied your examples to my spreadsheets and it fits perfectly. Thank you again for your detailed and clear presentation of the various options and for the very quick help. :-) — Sprössling, Mar 23 '23 at 08:22

score 0 · Answer 1 · answered Mar 17 '23 at 13:12

You can replace the comma-join with CROSS APPLY (from Oracle 12):

SELECT O.ID
     , O.name
     , trim(COLUMN_VALUE) AS service
     , c.name AS city
FROM   Organization o
       CROSS APPLY xmltable(('"' || REPLACE(o.service, ',', '","') || '"'))
       INNER JOIN Relation rel ON o.id = rel.oid
       INNER JOIN City c ON rel.cid  = c.id 
WHERE  o.service IS NOT NULL

or CROSS JOIN (in earlier versions):

SELECT O.ID
     , O.name
     , trim(COLUMN_VALUE) AS service
     , c.name AS city
FROM   Organization o
       CROSS JOIN xmltable(('"' || REPLACE(o.service, ',', '","') || '"'))
       INNER JOIN Relation rel ON o.id = rel.oid
       INNER JOIN City c ON rel.cid  = c.id 
WHERE  o.service IS NOT NULL

Or you can mix the ANSI join syntax and Oracle's legacy comma join syntax if the comma join is after the ANSI joins:

SELECT O.ID
     , O.name
     , trim(COLUMN_VALUE) AS service
     , c.name AS city
FROM   Organization o
       INNER JOIN Relation rel ON o.id = rel.oid
       INNER JOIN City c ON rel.cid  = c.id,
       xmltable(('"' || REPLACE(o.service, ',', '","') || '"'))
WHERE  o.service IS NOT NULL

Or you can use the legacy comma-joins throughout:

SELECT O.ID
     , O.name
     , trim(COLUMN_VALUE) AS service
     , c.name AS city
FROM   Organization o,
       xmltable(('"' || REPLACE(o.service, ',', '","') || '"')),
       Relation rel, 
       City c
WHERE  o.service IS NOT NULL 
AND    o.id = rel.oid
AND    rel.cid  = c.id

Which, for the sample data:

CREATE TABLE Organization (id, name, service) AS
SELECT 1, 'CEO',       '1, 2, 4' FROM DUAL UNION ALL
SELECT 2, 'ALF',       '1, 3, 5' FROM DUAL UNION ALL
SELECT 3, 'FRGT',      NULL      FROM DUAL UNION ALL
SELECT 4, 'OFFICE_IT', '1, 2, 7' FROM DUAL;

CREATE TABLE City (id, name) AS
SELECT 10, 'Berlin' FROM DUAL UNION ALL
SELECT 20, 'Paris'  FROM DUAL UNION ALL
SELECT 30, 'London' FROM DUAL UNION ALL
SELECT 40, 'Rome'   FROM DUAL;

CREATE TABLE Relation (oid, cid) AS
SELECT 1, 10 FROM DUAL UNION ALL
SELECT 2, 20 FROM DUAL UNION ALL
SELECT 3, 30 FROM DUAL UNION ALL
SELECT 4, 40 FROM DUAL;

All output:

ID	NAME	SERVICE	CITY
1	CEO	1	Berlin
1	CEO	2	Berlin
1	CEO	4	Berlin
2	ALF	1	Paris
2	ALF	3	Paris
2	ALF	5	Paris
4	OFFICE_IT	1	Rome
4	OFFICE_IT	2	Rome
4	OFFICE_IT	7	Rome

fiddle

Nested SQL query or subquery

1 Answers1