locked
4 tables inner join RRS feed

  • Question

  • User197255166 posted

    Hello guys, I have 4 tables. My main table is Products. Other tables product specification etc.

    Other tables also have features of the product. Multiple lines. I want to bring this into one line.

    Which path should I follow while doing this?

    For example the tables

    select * from Product

    select TOP 5 * from Product_SpecificationAttribute_Mapping where ProductId=82474

    select TOP 5 * from SpecificationAttributeOption where Id IN(9,47)

    select TOP 5 * from SpecificationAttribute where Name('Cinsiyet','Kategori')

    Thursday, December 31, 2020 7:34 AM

All replies

  • User-595703101 posted

    Merhaba Aliyilmaz,

    Please check following SQL code sample

    /*
    create table Product (
    ProductId int,
    Code varchar(10)
    )
    create table Product_SpecificationAttribute_Mapping  (
    ProductId int,
    AttributeId int
    )
    truncate table product
    insert into Product values (1,'AAAAA'),(2,'BBBBB')
    
    insert into Product_SpecificationAttribute_Mapping values (1,11),(1,12),(1,13),(2,29),(2,28)
    */
    with cte as (
    select
    	p.ProductId,
    	a.AttributeId,
    	columnid = ROW_NUMBER() over (partition by a.ProductId order by a.AttributeId)
    from Product p
    left join Product_SpecificationAttribute_Mapping a
    	on p.ProductId = a.ProductId
    )
    select
    	ProductId,
    	max(Attribute_1) as Attribute_1,
    	max(Attribute_2) as Attribute_2,
    	max(Attribute_3) as Attribute_3,
    	max(Attribute_4) as Attribute_4,
    	max(Attribute_5) as Attribute_5
    from (
    	select
    		ProductId,
    		case when columnid = 1 then AttributeId end as Attribute_1,
    		case when columnid = 2 then AttributeId end as Attribute_2,
    		case when columnid = 3 then AttributeId end as Attribute_3,
    		case when columnid = 4 then AttributeId end as Attribute_4,
    		case when columnid = 5 then AttributeId end as Attribute_5
    	from cte
    ) tbl
    group by ProductId
    
    

    This will display attributes of a product in 5 side by side columns named Attribute_1 ... Attribute_5

    Please refer to Display Data in Multiple Columns using SQL tutorial for a similar use case where data of a single column is displayed in multiple columns

    You will see, it is similar to a SQL PIVOT query but for pivot we are actually using values of the attributes. This is the difference

    Thursday, December 31, 2020 8:25 AM