select Product and Category

select.sql

if object_id('dbo.DETAIL') is not null
    drop table dbo.DETAIL
go

if object_id('dbo.PRODUCT') is not null
    drop table dbo.PRODUCT
go

if object_id('dbo.CATEGORY') is not null
    drop table dbo.CATEGORY
go

create table dbo.PRODUCT
(
    PRODUCT_ID int identity primary key,
    NAME varchar(100) null,
    DESCRIPTION varchar(255) null,
    CREATE_DATE datetime default getdate(),
    PRICE money not null
)
go

create table dbo.CATEGORY
(
    CATEGORY_ID int identity primary key,
    NAME varchar(100) null,
    DESCRIPTION varchar(255) null,
    CREATE_DATE datetime default getdate()
)

create table dbo.DETAIL
(
    DETAIL_ID int identity primary key,
    PRODUCT_ID int references dbo.PRODUCT(PRODUCT_ID),
    CATEGORY_ID int references dbo.CATEGORY(CATEGORY_ID),
    CREATE_DATE datetime default getdate()
)

exec sp_addextendedproperty  @name = 'MS_Description', @value = N'Продукты',
                            @level0type = 'SCHEMA', @level0name = 'dbo',
                            @level1type = 'TABLE', @level1name = 'PRODUCT'
go

exec sp_addextendedproperty  @name = 'MS_Description', @value = N'Идентификатор продукта',
                            @level0type = 'SCHEMA', @level0name = 'dbo',
                            @level1type = 'TABLE', @level1name = 'PRODUCT',
                            @level2type = 'COLUMN', @level2name = 'PRODUCT_ID'
go

insert into dbo.PRODUCT(NAME, PRICE)
values ('Product #1', 1.0),
       ('Product #2', 2.0),
       ('Product #3', 3.0)

insert into dbo.CATEGORY(NAME)
values ('Category #1'),
       ('Category #2'),
       ('Category #3')

insert  into dbo.DETAIL(PRODUCT_ID, CATEGORY_ID)
values  (1, 1),
        (1, 2),
        (2, 1)

select *
from dbo.PRODUCT

select *
from dbo.CATEGORY

select *
from dbo.DETAIL

select      pr.NAME as PRODUCT_NAME,
            cat.NAME as CATEGORY_NAME
from        dbo.PRODUCT as pr
left join   dbo.DETAIL as det
            on pr.PRODUCT_ID = det.PRODUCT_ID
left join   dbo.CATEGORY as cat
            on cat.CATEGORY_ID = det.CATEGORY_ID