4 tables inner join

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')

Answer 1

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