Tablo birleştirme ve gruplama hk

18-07-2022, 13:26:54

Bu tabloyu ISSUE tablosuyla birleştirmek istiyorum. Aynı issue idsine sahip verilerin gruplu gelmesini istiyorum ama yapamadım. Joinleyince iki farklı satır olarak geliyor. Json çıktısını ve 2. tabloyu aşağıya bırakıyorum. Diğer tablo adı ISSUE .
İstediğim şey IDSI 12 olan verinin tek gelmesi ve içerisinde FILE_TYPE,FILE_NAME verilerinin gruplu gelmesi.

[
    {
        "ID": 12,
        "ISSUE_HEADER": "What is Lorem Ipsum? What is Lorem Ipsum? What is Lorem Ipsum? What is Lorem Ipsum?",
        "ISSUE_CONTENT": "Lorem Ipsum ",
        "ISSUE_IMAGE": "issue3.jpeg",
        "ISSUE_NO": "101",
        "APPROVE_DATE": "2022-07-17T00:00:00.000Z",
        "NEWSPAPER_PUBLISH_DATE": "2022-07-13T00:00:00.000Z",
        "NEWSPAPER_PUBLISH_NO": "202",
        "COMPOSITION": "55",
        "VOLUME": "2",
        "AUTHOR": "",
        "RELEASE_DATE": "2022-07-15T15:17:56.000Z",
        "INSERT_DATE": "2022-07-15T15:17:56.000Z",
        "FILE_TYPE": "1",
        "FILE_NAME": "test.pdf"
    },
    {
        "ID": 12,
        "ISSUE_HEADER": "What is Lorem Ipsum? What is Lorem Ipsum? What is Lorem Ipsum? What is Lorem Ipsum?",
         "ISSUE_CONTENT": "Lorem Ipsum ",
        "ISSUE_IMAGE": "issue3.jpeg",
        "ISSUE_NO": "101",
        "APPROVE_DATE": "2022-07-17T00:00:00.000Z",
        "NEWSPAPER_PUBLISH_DATE": "2022-07-13T00:00:00.000Z",
        "NEWSPAPER_PUBLISH_NO": "202",
        "COMPOSITION": "55",
        "VOLUME": "2",
        "AUTHOR": "",
        "RELEASE_DATE": "2022-07-15T15:17:56.000Z",
        "INSERT_DATE": "2022-07-15T15:17:56.000Z",
        "FILE_TYPE": "2",
        "FILE_NAME": "test.docx"
    }
]

18-07-2022, 13:30:22

#2

murqlegend

UNION'u araştır, tek satır olarak veri verir.

Union öperatörü, iki veya daha fazla Select durumlu sonuç kümesini birleştirmek için kullanılır. Kullanımı şu şekilde:

SELECT column_name(s) FROM  table_name1
UNION
SELECT column_name(s)  FROM table_name2

18-07-2022, 18:37:46

#3

suleymanyaldiz

union ile yapamazsin
Yukaridaki cevapta da belirtildigi gibi
Union öperatörü, iki veya daha fazla Select durumlu sonuç kümesini birleştirmek için kullanılır

group by ve string aggregate yapman lazim

select issues."ID", issues."ISSUE_HEADER", issues."ISSUE_CONTENT", issues."ISSUE_IMAGE", issues."ISSUE_NO", issues."APPROVE_DATE", issues."NEWSPAPER_PUBLISH_DATE", issues."NEWSPAPER_PUBLISH_NO", issues."COMPOSITION", issues."VOLUME", issues."AUTHOR", issues."RELEASE_DATE", issues."INSERT_DATE",
--- string aggregate syntax ---
files.file_type & files.file_name as issue_files
--- string aggregate syntax ---
from issues
left join files on issues.id = files.issue_id
group by issues."ID", issues."ISSUE_HEADER", issues."ISSUE_CONTENT", issues."ISSUE_IMAGE", issues."ISSUE_NO", issues."APPROVE_DATE", issues."NEWSPAPER_PUBLISH_DATE", issues."NEWSPAPER_PUBLISH_NO", issues."COMPOSITION", issues."VOLUME", issues."AUTHOR", issues."RELEASE_DATE", issues."INSERT_DATE",

19-07-2022, 11:50:33

#4

gurbuzemre

suleymanyaldiz adlı üyeden alıntı: mesajı görüntüle

union ile yapamazsin
Yukaridaki cevapta da belirtildigi gibi
Union öperatörü, iki veya daha fazla Select durumlu sonuç kümesini birleştirmek için kullanılır

group by ve string aggregate yapman lazim

select issues."ID", issues."ISSUE_HEADER", issues."ISSUE_CONTENT", issues."ISSUE_IMAGE", issues."ISSUE_NO", issues."APPROVE_DATE", issues."NEWSPAPER_PUBLISH_DATE", issues."NEWSPAPER_PUBLISH_NO", issues."COMPOSITION", issues."VOLUME", issues."AUTHOR", issues."RELEASE_DATE", issues."INSERT_DATE",
--- string aggregate syntax ---
files.file_type & files.file_name as issue_files
--- string aggregate syntax ---
from issues
left join files on issues.id = files.issue_id
group by issues."ID", issues."ISSUE_HEADER", issues."ISSUE_CONTENT", issues."ISSUE_IMAGE", issues."ISSUE_NO", issues."APPROVE_DATE", issues."NEWSPAPER_PUBLISH_DATE", issues."NEWSPAPER_PUBLISH_NO", issues."COMPOSITION", issues."VOLUME", issues."AUTHOR", issues."RELEASE_DATE", issues."INSERT_DATE",

Farkettim union ile olmayacağını müsait olduğumda deneyim hocam