Recursive Query

Transact-SQL
1

Hi,

I am having a Trouble in Making This Type of Query

ID -------------- Name ----------- ParentID
1 -------------- Food ------------------- 0
2 -------------- Burger ------------------- 1
3 -------------- Beef Burger ----------- 2
4 -------------- KFC ---------------------- 3

I want to stop User To making 4 ID with Parent ID 3
but if user want to make 4 id with parent ID 2 then query return Ok.

i hope u understand

2

hi

you can create a check constraint
on ID=4 and ParentID = 3

this will not allow anybody to put ..!!! :slight_smile:
hope this helps !!!

3

is there any possibility for this

4

please see this !!!

https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-check-constraints

5

Those are 2 distinctly separate data entities and you should normalize it. How can you a store name in the same table as food items?. You need a store table separate and food items table separate and then a store food items table separate.

Normalize as follows or close to this

create table #foodtypes(foodtypeid int, foodtype nvarchar(150))
insert into #foodtypes
select 1, 'Beef' union
select 2, 'Tofu' union
select 3, 'Vegan' 

create table #fooditems(fooditemid int, fooditem nvarchar(150), foodtypeid int)
insert into #fooditems
select 2 as fooditemid, 'Burger' as fooditem, 1 as foodtypeid

create table #stores(storeid int, storename nvarchar(150))
insert into #stores(storeid, storename)
select 1, 'KFC' union
select 2, 'Mc D' union
select 3, '5 Dudes'


create table #foodstores(storeid int, fooditemid int)

insert into #foodstores
select storeid, fi.fooditemid
  from #stores s
  cross apply(select * from #fooditems) fi
  where storename = 'KFC' 

select * 
  from #foodstores;

drop table #foodtypes
drop table #fooditems
drop table #stores
drop table #foodstores
6

Yeah... I agree. It's not like this is a hierarchical bill of materials.

7

Well my Psychopath Team Lead Give Me This Type of Task
and at the end he also dont know how to do this :smiley: :smiley:

8

@immad ,
BWAAAA_HAAAA!!! I have a real appreciation for that!. Sounds like you're still having problems. Post back if you are and we'll get to it with the understand that we have to help you with the way it is.

9

The key here is simply "level detection" within the hierarchy and also knowing what kind of entry the new row is.

I say "simply" but it won't be able to be done unless there's something that will preclassify the new KFC row as something that belongs only to the 2nd level of the hierarchy. It's nearly impossible to enforce a hierarchical order when free-form input is allowed.

So, with that, is there anything that can preclassify rows as to "type" or "level"?

10

team lead should lead....to success. I dont like it when no technical team leads drive technical solutions. I always tell them "I could do what you asked but it would be so much better if you give me the requirements in plain old English. just give me the requirements and you hired me to implement right?"

11

Some times, there is no solution because of the Devils in the Data. You can't blame the Team Lead when he cannot do something that is bloody impossible to do.

12

agreed. but he/she should not influence a bad design either. Put both the storename and the good they provide in the same table. i guess it could be done....