help to complete the final product table

Question

User448619955 posted

Hi,

I'm trying to develop a console app which return some products.

I have three tables : Product, Specs and ProductSpecs.

                                  Product

ID	ModelCode	ParentId
1	SM-F22	Null
2	SM-F22Black	1
3	SM-F22White	1
4	SM-A10	Null

                   Specs

Id	Specname
1	Display
2	Memory
3	CPU
4	Color
5	Other

ProductSpec

Id	productId	SpecId	Value
1	1	5	Waterproof
2	2	1	16*10
3	2	2	64GB
4	2	3	SnapDragonXXX
5	2	4	Black
6	3	2	128GB
7	3	4	White
8	4	1	17*12
9	4	2	128GB
10	4	3	Snapdragon XX
11	4	4	blue
12	4	5	Waterproof

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:

productId	ModelCode	SpecName	Value
2	SM-F22Black	Display	16*10
2	SM-F22Black	Memory	64GB
2	SM-F22Black	CPU	SnapDragonXXX
2	SM-F22Black	Color	Black
2	SM-F22Black	Other	Waterproof
3	SM-F22White	Other	Waterproof
3	SM-F22White	Memory	128GB
3	SM-F22White	Color	White
4	SM-A10	Display	17*12
4	SM-A10	Memory	128GB
4	SM-A10	CPU	Snapdragon XX
4	SM-A10	Color	blue
4	SM-A10	Other	Waterproof

Answer 1

User-474980206 posted

you requirements don't quite look right, but its:

select *
from Products p
join ProductSpec ps on ps.ProductId = p.ID
join Specs s on s.ID = ps.SpecId
where
   p.Id not in (select ParentId from Products)  -- no children
   or p.ParentId is not null                             -- has a parent id

Answer 2

User-719153870 posted

Hi vahid.ch,

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
(1,1,5,'Waterproof'),(2,2,1,'16*10'),
(3,2,2,'64GB'),(4,2,3,'SnapDragonXXX'),
(5,2,4,'Black'),(6,3,2,'128GB'),
(7,3,4,'White'),(8,4,1,'17*12'),
(9,4,2,'128GB'),(10,4,3,'Snapdragon XX'),
(11,4,4,'blue'),(12,4,5,'Waterproof')

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!

Best Regard,

Yang Shen