locked
help to complete the final product table RRS feed

  • 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
    Monday, May 18, 2020 1:52 PM

All replies

  • 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

    Monday, May 18, 2020 3:17 PM
  • 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

    Tuesday, May 19, 2020 9:45 AM