Add 1 Level_Two_ID using sql

Question

User-367318540 posted

i have below table with data

Create table #tbl_Account_L_One  (Level_one_ID int,Level_One_Name varchar(50))
insert into #tbl_Account_L_One values(1,'Asset'),(2,'Equity'),(3,'Libility')

Create table  #tbl_Account_L_Two (Level_Two_ID int,Level_one_ID int,Level_One_Name varchar(50))
insert into #tbl_Account_L_Two values(11,1,'Capital'),(12,1,'C'),(21,2,'Libility')

Below store procedure to increase 1 in Level_Two_ID.

ALTER Procedure [dbo].[Sp_Generate_LeveTwo_ID]

as begin


;with cte as (select 1 as L_Two_ID

UNION ALL

select top (1) L_Two_ID + 1 as L_Two_ID

from  tbl_Account_L_Two t order by  t.L_Two_ID DESC)

select top 1 L_Two_ID

from cte ORDER BY L_Two_ID DESC
end

Create table  #tbl_Account_L_Two (Level_Two_ID int,Level_one_ID int,Level_One_Name varchar(50))
insert into #tbl_Account_L_Two values(11,1,'Capital'),(12,1,'C'),(21,2,'Libility')

1  level_One_ID exit then it will be 11,then again check if 11 again level_One_ID exit then it will be 12.if there is not level_One_ID exit of 2 then 21 will be ,if 3 level_One_ID does not exit then level_Two_ID will be 31

Means that before insert data into tbl_Account_L_Two ,it check level_one_ID ,if exit then add 1,

Answer 1

User1535942433 posted

Hi akhterr,

1  level_One_ID exit then it will be 11,then again check if 11 again level_One_ID exit then it will be 12.if there is not level_One_ID exit of 2 then 21 will be ,if 3 level_One_ID does not exit then level_Two_ID will be 31

Accroding to your description,I don't understand your requirment clearly.Could you tell us your reqiurment clearly and concisely.

Accroding to your query,I  create a test and I could get Level_Two_ID=22.I'm guessing that you need to check the Level_One_ID then add 1 in Level_Two_ID.

You could post more details to us.It will help us to solve your problem.

Best regards,

Yijing Sun

Answer 2

User-367318540 posted

Below is image 

link please view

https://ibb.co/gjXG0HW

Answer 3

User475983607 posted

The TSQL is very basic.

CREATE TABLE #tbl_account_l_one 
( 
    level_one_id   INT, 
    level_one_name VARCHAR(50) 
) 

CREATE TABLE #tbl_account_l_two 
( 
    level_two_id   INT, 
    level_one_id   INT, 
    level_one_name VARCHAR(50) 
) 


INSERT INTO #tbl_account_l_one 
VALUES     (1, 'Asset'), 
           (2,'Equity'), 
           (3,'Libility')


insert into #tbl_Account_L_Two 
values	(11,1,'Capital'),
		(12,1,'C'),
		(21,2,'Libility')

SELECT	o.level_one_name, 
		t.level_two_id, 
		t.level_one_name AS [LevelTwoName] 
FROM #tbl_account_l_one AS o
	JOIN #tbl_Account_L_Two t ON o.level_one_id = t.level_one_id

to get the UI like you want has nothing to do with TSQL.  It's up to you to come up with the design.  If you use standard HTML then you can merge rows.  Pretty basic stuff...

https://www.w3schools.com/tags/att_td_rowspan.asp

Answer 4

User-367318540 posted

Hi mgebhard,

i do not want this,i want that how can i add 11,12,13 ,before inserting row into #tbl_account_l_two

Answer 5

User475983607 posted

i do not want this,i want that how can i add 11,12,13 ,before inserting row into #tbl_account_l_two

I have no idea what you're asking.  I simply used your DDL and DML.  What is 11, 12,and 13 and why are you unable to add records to #tbl_account_l_two?

Answer 6

User-367318540 posted

Hi mgebhard,

 #tbl_account_l_two have three column ,(level_two_id,level_one_ID,level_Two_name).

Before inserting record in #tbl_Account_L_Two ,it check that ,if Level_One_ID is 1 inserting into table then level_two_ID will be (11),then second row will inserting with same Level_One_ID 1 and level_two_Id 11 is exit ,then level_Two_id will be 12 for second record ,

if Level_one_ID is 2 ,then level_two_Id will be 21 ,same for second row inserting if level_one_ID (2) and Level_Two_ID(21) exit ,then level_two_id will be 22 before inserting .

I am inserting from webform asp.net 

Answer 7

User1535942433 posted

Hi akhterr,

Accroding to your description,I'm guessing that you have a total category and every category have sub categories.You need to check if exist Level_One_ID in the tbl_Account_L_One.Then ,if there are more than one level_one_id,the level_two_id will add 1.

As far as I think,you need to select count the level_one_id in the tbl_Account_L_Two. Then the level_two_id will add 1.

More details,you could refer to below codes:

IF EXISTS(SELECT*
FROM #tbl_account_l_one AS o
	JOIN #tbl_Account_L_Two t ON o.level_one_id = t.level_one_id)
BEGIN
select MAX(level_two_id) + 1 AS Result FROM #tbl_account_l_two  where (select count(level_one_id) from #tbl_account_l_two where level_one_id='1')>0 and level_one_id='1'
END

Best regards,

Yijing Sun