0

I'm trying to create an SQL query allowing me to do this:

I have 3 tables in SQL Server 2017:

CREATE TABLE [dbo].[PRODUCTCATEGORY]
(
    [PROD_ID] [int] NOT NULL,
    [CAT_ID] [int] NOT NULL 

    CONSTRAINT [PK_PRODUCTCATEGORY] 
        PRIMARY KEY CLUSTERED ([PROD_ID] ASC, [CAT_ID] ASC)
)

CREATE TABLE [dbo].[CATEGORY]
(
    [CAT_ID] [int] IDENTITY(1,1) NOT NULL,
    [CAT_TITLE] [varchar](50) NOT NULL

    CONSTRAINT [PK_CATEGORY] 
        PRIMARY KEY CLUSTERED ([CAT_ID] ASC)
)

CREATE TABLE [dbo].[PRODUCT]
(
    [PROD_ID] [int] IDENTITY(1,1) NOT NULL,
    [PROD_TITLE] [varchar](50) NOT NULL

    CONSTRAINT [PK_PRODUCT] 
        PRIMARY KEY CLUSTERED ([PROD_ID] ASC)
)
  • A product can have 1 to many categories
  • A category can have 1 to many products
PROD_ID PROD_TITLE
1 Book 1
2 Book 2
CAT_ID CAT_TITLE
1 Cat 1
2 Cat 2
3 Cat 3
PROD_ID CAT_ID
1 1
1 2
2 1
2 3

I would like to retrieve this:

| CAT_ID  |CAT_TITLE | PRODUCTS                                                                |
|:------- |:--------:|:------------------------------------------------------------------------|
| 1       | Cat 1    |[{"PROD_ID":1,"PROD_TITLE":"Book 1"},{"PROD_ID":2,"PROD_TITLE":"Book 2"}]|
| 2       | Cat 2    |[{"PROD_ID":1,"PROD_TITLE":"Book 1"}]                                    |
| 3       | Cat 3    |[{"PROD_ID":2,"PROD_TITLE":"Book 2"}]                                    |

Thanks for your help

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
fanfan1973
  • 11
  • 3

1 Answers1

0

I just found this, using FOR JSON:

https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver15

I think something like this might work:

SELECT c.CAT_ID, c.CAT_TITLE, 
(
   SELECT p.PROD_ID, p.PROD_TITLE
   FROM PRODUCT p
   JOIN PRODUCTCATEGORY pc ON pc.PROD_ID = p.PROD_ID
   WHERE pc.CAT_ID = c.CAT_ID
   FOR JSON PATH
) AS ProductsAsJson
FROM CATEGORY c
Puzzled Developer
  • 41
  • 2