I have a query from two tables ...
The first table in which the goods are in the form of tree accounts ....
The second table in which the sale is linked to the first table.
For example, now I have the first table.
Question A sort query is required ..
I get the cars for their condition ... electronics for their condition
Of Table II.
With attachments, I want to create a query.
Database Script :
USE [To_Test]
GO
/****** Object: Table [dbo].[Items] Script Date: 11/10/1438 05:54:35 م ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Items](
[Id_ItemS] [int] NOT NULL,
[Name_ar] [nvarchar](50) NULL,
[Number_Id] [int] NULL,
[Basic] [bit] NULL,
CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED
(
[Id_ItemS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sell_items](
[Id_Sell] [int] IDENTITY(1,1) NOT NULL,
[Id_items] [int] NULL,
[price] [money] NULL,
CONSTRAINT [PK_Sell_items] PRIMARY KEY CLUSTERED
(
[Id_Sell] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Items] ([Id_ItemS], [Name_ar], [Number_Id], [Basic]) VALUES (1, N'Cars', NULL, 1)
INSERT [dbo].[Items] ([Id_ItemS], [Name_ar], [Number_Id], [Basic]) VALUES (2, N'electronics', NULL, 1)
INSERT [dbo].[Items] ([Id_ItemS], [Name_ar], [Number_Id], [Basic]) VALUES (3, N'Toyota', 1, 1)
INSERT [dbo].[Items] ([Id_ItemS], [Name_ar], [Number_Id], [Basic]) VALUES (4, N'Nissan', 1, 1)
INSERT [dbo].[Items] ([Id_ItemS], [Name_ar], [Number_Id], [Basic]) VALUES (5, N'Camry', 3, 1)
INSERT [dbo].[Items] ([Id_ItemS], [Name_ar], [Number_Id], [Basic]) VALUES (6, N'2015', 5, 0)
INSERT [dbo].[Items] ([Id_ItemS], [Name_ar], [Number_Id], [Basic]) VALUES (7, N'2016', 5, 0)
INSERT [dbo].[Items] ([Id_ItemS], [Name_ar], [Number_Id], [Basic]) VALUES (8, N'2017', 5, 0)
INSERT [dbo].[Items] ([Id_ItemS], [Name_ar], [Number_Id], [Basic]) VALUES (9, N'TV', 2, 1)
INSERT [dbo].[Items] ([Id_ItemS], [Name_ar], [Number_Id], [Basic]) VALUES (10, N'LG', 9, 0)
INSERT [dbo].[Items] ([Id_ItemS], [Name_ar], [Number_Id], [Basic]) VALUES (11, N'Samsung', 9, 0)
SET IDENTITY_INSERT [dbo].[Sell_items] ON
INSERT [dbo].[Sell_items] ([Id_Sell], [Id_items], [price]) VALUES (1, 11, 2500.0000)
INSERT [dbo].[Sell_items] ([Id_Sell], [Id_items], [price]) VALUES (2, 10, 2300.0000)
INSERT [dbo].[Sell_items] ([Id_Sell], [Id_items], [price]) VALUES (3, 6, 20000.0000)
INSERT [dbo].[Sell_items] ([Id_Sell], [Id_items], [price]) VALUES (4, 7, 270000.0000)
SET IDENTITY_INSERT [dbo].[Sell_items] OFF
ALTER TABLE [dbo].[Items] WITH CHECK ADD CONSTRAINT [FK_Items_Items1] FOREIGN KEY([Number_Id])
REFERENCES [dbo].[Items] ([Id_ItemS])
GO
ALTER TABLE [dbo].[Items] CHECK CONSTRAINT [FK_Items_Items1]
GO
ALTER TABLE [dbo].[Sell_items] WITH CHECK ADD CONSTRAINT [FK_Sell_items_Items] FOREIGN KEY([Id_items])
REFERENCES [dbo].[Items] ([Id_ItemS])
GO
ALTER TABLE [dbo].[Sell_items] CHECK CONSTRAINT [FK_Sell_items_Items]
GO
view
WITH Recursive_CTE AS (
SELECT Items.Id_ItemS, Items.Name_ar, Items.Number_Id, 1 AS RecursionLevel, CAST(Items.Id_ItemS AS varchar(100)) AS Hierarchy, Sell_items.Id_Sell
FROM Items INNER JOIN
Sell_items ON Items.Id_ItemS = Sell_items.Id_items
WHERE (Items.Id_ItemS = 1)
UNION ALL
SELECT Items.Id_ItemS, Items.Name_ar, Items.Number_Id, 1 AS RecursionLevel, CAST(Hierarchy + ':' + CAST(Items.Id_ItemS AS varchar(100)) AS varchar(100)) AS Hierarchy, Sell_items.Id_Sell
FROM Recursive_CTE AS parent INNER JOIN
Items ON parent.Id_items = Items.Number_Id INNER JOIN
Sell_items ON Items.Id_ItemS = Sell_items.Id_items )
SELECT * FROM Recursive_CTE ORDER BY Hierarchy
but The result is null
I wish to modify the query ?
The desired result enter image description here
I came to answer the question, very excellent
WITH Recursive_CTE (Id_ItemS,Name_ar,Number_Id,RecursionLevel,Hierarchy) AS (
SELECT Items.Id_ItemS, Items.Name_ar, Items.Number_Id, 1 AS RecursionLevel, CAST(Items.Id_ItemS AS varchar(100)) AS Hierarchy
FROM Items
where (Id_ItemS=1)
UNION ALL
SELECT Items.Id_ItemS, Items.Name_ar, Items.Number_Id, RecursionLevel+1 AS RecursionLevel, CAST(Hierarchy + ':' + CAST(Items.Id_ItemS AS varchar(100)) AS varchar(100)) AS Hierarchyss
FROM Recursive_CTE AS parent INNER JOIN
Items ON parent.Id_items = Items.Number_Id)
SELECT Id_ItemS, Name_ar, Number_Id, RecursionLevel, Hierarchy, Sell_items.Id_Sell, Sell_items.price, Sell_items.Id_items2
FROM Recursive_CTE INNER JOIN
Sell_items ON Sell_items.Id_items2 = Recursive_CTE.Id_items
ORDER BY Hierarchy
Id_ItemS Name_ar Number_Id RecursionLevel Hierarchy Id_Sell price Id_items2
6 2015 5 4 1:3:5:6 3 20000.00 6
7 2016 5 4 1:3:5:7 4 270000.00 7
7 2016 5 4 1:3:5:7 5 998877.00 7