Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- //Sql schema __________________________________________________________________________________
- create table Products
- (
- ID int primary key identity (1, 1),
- NAME nvarchar(128) not null
- );
- create table Categories
- (
- ID int primary key identity (1, 1),
- NAME nvarchar(128) not null
- );
- create table ProductCategories
- (
- ID int primary key identity (1, 1),
- ProductId int,
- CategoryId int
- );
- INSERT INTO Products
- ([name])
- VALUES
- ('Coca-cola'),
- ('iPhone 9000 pro max ultra rtx noscope 360(R)'),
- ('umbrella'),
- ('cat food')
- ;
- INSERT INTO Categories
- ([name])
- VALUES
- ('drinks'),
- ('electronics'),
- ('apple products')
- ;
- insert into ProductCategories
- ([ProductId],[CategoryId])
- VALUES
- (1,1),
- (2,2),
- (2,3)
- ;
- //__________________________________________________________________________________________________
- //В задании сказано: "Напишите SQL запрос для выбора всех пар «Имя продукта – Имя категории». Если у продукта нет категорий, то его имя //все равно должно выводиться.", ниже я привёл два варианта решения, поскольку не совсем понял, какой из них лучше подойдёт.
- Select p.name as Product,c.name as Category from Products p
- left join ProductCategories pc on p.id = pc.ProductId
- left join Categories c on pc.CategoryId = c.Id
- //********************************Result*********************************************
- Coca-cola drinks
- iPhone 9000 pro max ultra rtx noscope 360(R) electronics
- iPhone 9000 pro max ultra rtx noscope 360(R) apple products
- umbrella (null)
- cat food (null)
- SELECT p.name as Product, STRING_AGG(c.name, ', ') AS Categories
- FROM Products p
- left join ProductCategories pc on p.id = pc.ProductId
- left join Categories c on pc.CategoryId = c.Id
- Group by p.name
- //***********************************Result******************************************
- Product Categories
- cat food (null)
- Coca-cola drinks
- iPhone 9000 pro max ultra rtx noscope 360(R) electronics, apple products
- umbrella (null)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement