Last post May 19, 2020 09:45 AM by Yang Shen
May 18, 2020 01:52 PM|vahid.ch|LINK
I'm trying to develop a console app which return some products.
I have three tables : Product, Specs and ProductSpecs.
The result should be as below:
1- All products that don't have any children are considered as Product.
2- all products that have Parentid will be a product and their parent shouldn't return in the result.
the result should be something like this:
May 18, 2020 03:17 PM|bruce (sqlwork.com)|LINK
you requirements don't quite look right, but its:
from Products p
join ProductSpec ps on ps.ProductId = p.ID
join Specs s on s.ID = ps.SpecId
p.Id not in (select ParentId from Products) -- no children
or p.ParentId is not null -- has a parent id
May 19, 2020 09:45 AM|Yang Shen|LINK
If you are talking about SQL Server tables, then the
Joins should be what you are looking fot.
Please check below demo:
create table #Product(ID int, ModelCode varchar(50), ParentId int)
create table #Specs( Id int, Specname varchar(50))
create table #ProductSpec( Id int, productId int, SpecId int, Value varchar(50))
insert into #Product values(1,'SM-F22',null),(2,'SM-F22Black',1),(3,'SM-F22White',1),(4,'SM-A10',null)
insert into #Specs values(1,'Display'),(2,'Memory'),(3,'CPU'),(4,'Color'),(5,'Other')
insert into #ProductSpec values
select * from #Product
select * from #Specs
select * from #ProductSpec
select ps.productId,p.ModelCode,s.Specname,ps.Value from #ProductSpec ps left join #Specs s on ps.SpecId=s.Id left join #Product p on ps.productId=p.ID where p.ID not in (select ParentId from #Product where ParentId is not null)
Here's the result of this demo:
If there's any misunderstanding, please clarify and provide more detailed information. Thanks!