Get all 1:n records from subtable

Question

I have this table INCIDENTCHAPTER. Each INCIDENTCHAPTER_ID is unique

INCIDENTCHAPTER_ID	....
1
2
3

And this sub-table LABEL. Each INCIDENTCHAPTER_ID must have a Name und Shortname, but the Help is optional. So it looks like this.

INCIDENTCHAPTER_ID	TEXTTYPE	TEXT
1	Name	Alert
1	Shortname	A
1	Help	Some Helptext
2	Name	Notification
2	Shortname	N
2	Help	Another Helptext
3	Name	Chapter One
3	Shortname	1

I have this sql:

SELECT
INCIDENTCHAPTER.INCIDENTCHAPTER_ID,
N.TEXT AS NAME,
SN.TEXT AS SHORTNAME,
H.TEXT AS HELP
FROM
REM_DBA.INCIDENTCHAPTER
LEFT JOIN LABEL N ON INCIDENTCHAPTER.INCIDENTCHAPTER_ID = N.INCIDENTCHAPTER_ID
LEFT JOIN LABEL SN ON INCIDENTCHAPTER.INCIDENTCHAPTER_ID = SN.INCIDENTCHAPTER_ID
LEFT JOIN LABEL H ON INCIDENTCHAPTER.INCIDENTCHAPTER_ID = H.INCIDENTCHAPTER_ID
WHERE
N.TEXTTYPE = 'Name' AND
SN.TEXTTYPE = 'ShortName' AND
H.TEXTTYPE = 'Help'

INCIDENTCHAPTER_ID	NAME	SHORTNAME	HELP
1	Alert	A	Some Helptext
2	Notification	N	Another Helptext

But it only gets me INCIDENTCHAPTER_ID 1 and 2. Because INCIDENTCHAPTER_ID 3 has no Help.

Whats the correct sql to make it look like:

INCIDENTCHAPTER_ID	NAME	SHORTNAME	HELP
1	Alert	A	Some Helptext
2	Notification	N	Another Helptext
3	Chapter One	1

Does this answer your question? [Oracle SQL pivot query](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) — astentx, Mar 10 '22 at 10:13

MT0 · Accepted Answer · 2022-03-10T10:43:33.463

Move the filters from the WHERE clause to the respective ON clauses:

SELECT ic.INCIDENTCHAPTER_ID,
       N.TEXT AS NAME,
       SN.TEXT AS SHORTNAME,
       H.TEXT AS HELP
FROM   REM_DBA.INCIDENTCHAPTER ic
       LEFT JOIN LABEL N
       ON (   ic.INCIDENTCHAPTER_ID = N.INCIDENTCHAPTER_ID
          AND N.TEXTTYPE = 'Name')
       LEFT JOIN LABEL SN
       ON (   ic.INCIDENTCHAPTER_ID = SN.INCIDENTCHAPTER_ID
          AND SN.TEXTTYPE = 'ShortName')
       LEFT JOIN LABEL H
       ON (   ic.INCIDENTCHAPTER_ID = H.INCIDENTCHAPTER_ID
          AND H.TEXTTYPE = 'Help')

If you put them in the WHERE clause then those values are mandatory and the LEFT JOINs effectively become INNER JOINs as there must be a value that matches the WHERE filters.

Which, for the sample data:

CREATE TABLE INCIDENTCHAPTER (INCIDENTCHAPTER_ID PRIMARY KEY) AS
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 3;

CREATE TABLE label (INCIDENTCHAPTER_ID, TEXTTYPE, TEXT) AS
SELECT 1, 'Name',      'Alert' FROM DUAL UNION ALL
SELECT 1, 'ShortName', 'A' FROM DUAL UNION ALL
SELECT 1, 'Help',      'Some Helptext' FROM DUAL UNION ALL
SELECT 2, 'Name',      'Notification' FROM DUAL UNION ALL
SELECT 2, 'ShortName', 'N' FROM DUAL UNION ALL
SELECT 2, 'Help',      'Another Helptext' FROM DUAL UNION ALL
SELECT 3, 'Name',      'Chapter One' FROM DUAL UNION ALL
SELECT 3, 'ShortName', '1' FROM DUAL;

Outputs:

INCIDENTCHAPTER_ID NAME SHORTNAME HELP

1 Alert A Some Helptext

2 Notification N Another Helptext

3 Chapter One 1 null

Or, you could use a single join and then PIVOT:

SELECT *
FROM   (
  SELECT ic.incidentchapter_id,
         l.texttype,
         l.text
  FROM   /*REM_DBA.*/INCIDENTCHAPTER ic
         LEFT OUTER JOIN LABEL l
         ON (   ic.INCIDENTCHAPTER_ID = l.INCIDENTCHAPTER_ID)
)
PIVOT (
  MAX(text) FOR texttype IN (
    'Name'      AS name,
    'ShortName' AS shortname,
    'Help'      AS help
  )
);

Which gives the same output for your sample data (but would not give the same data if there were multiple rows for a texttype in a chapter).

db<>fiddle here

Get all 1:n records from subtable

1 Answers1