CODEWITHSUNDEEP
sql-servert-sql
Cristen Rafalko
Cristen Rafalko

Reputation: 85

T-SQL - Select conditions Attributes in Table

I have problems how to search with multiple conditions.

Example: you want to buy shoes with attributes "Color Red" and "Size 10".

In my result, I don't know how to remove a shoes that have only the right color or size (I mean: I want to select only shoes have all attributes).

Declare @Tbl_Atributes Table
                       (
                            [NameAttribute] nvarchar(250),
                            [ValueAttribute] nvarchar(250)
                       )

Declare @Tbl_Product Table
                     (
                        [Code] int,
                        [Name] nvarchar(250),
                        [NameAttribute] nvarchar(250),
                        [ValueAttribute] nvarchar(250)
                     )

Insert Into @Tbl_Atributes 
values ('Color', 'Red'), ('Size', '10')

Insert Into @Tbl_Product ([Code], [Name], [NameAttribute], [ValueAttribute])
values ('1', 'Nike', 'Color', 'Red'),
       ('1', 'Nike', 'Color', 'Blue'),
       ('1', 'Nike', 'Size', '10'),
       ('2', 'Adidas', 'Size', '10')

I want to get products have all NameAttribute in @Tbl_Atributes.

My first attempt:

select tp.*
from @Tbl_Product tp
inner join @Tbl_Atributes as ta on tp.NameAttribute = ta.NameAttribute
                                and tp.ValueAttribute = ta.ValueAttribute

I meet the problem: product Adidas have 1 attribute Name: 'Size', and it's still in the result. (We need 2 attribute Name: Size and Color, only Nike have all)

Please, help me resolve. Thanks.

Upvotes: 2

Views: 795

Answers (2)

Murat Oğuzhan
Murat Oğuzhan

Reputation: 885

I found solution like that;

Declare @Tbl_Atributes Table(
[NameAttribute] nvarchar(250)
,[ValueAttribute] nvarchar(250))

Declare @Tbl_Product Table(
    [Code] int,
    [Name] nvarchar(250),
    [NameAttribute] nvarchar(250)
    ,[ValueAttribute] nvarchar(250))

Insert Into @Tbl_Atributes values ('Color', 'Red'), ('Size', '10')

Insert Into @Tbl_Product  ([Code],[Name], [NameAttribute], [ValueAttribute])
values ('1','Nike','Color','Red'),('1','Nike','Color','Blue'),('1','Nike','Size','10')
        ,('2','Addidas','Size','10')


select tmp.* 
from (select tp1.Code, tp1.Name
        from @Tbl_Product tp1 
        group by tp1.Code,tp1.Name) as tmp    
where (select COUNT(1) from @Tbl_Atributes)=
        (select count(1) 
        from @Tbl_Product tp
        join @Tbl_Atributes ta on tp.NameAttribute=ta.NameAttribute and tp.ValueAttribute=ta.ValueAttribute
        where tp.Code=tmp.Code)

Upvotes: 0

forpas
forpas

Reputation: 164139

You can use EXISTS:

select distinct [Code], [Name] from Tbl_Product t
where
  exists (
    select 1 from Tbl_Product 
    where [Code] = t.[Code] and [NameAttribute] = 'Color' and [ValueAttribute] = 'Red'
  )
  and 
  exists (
    select 1 from Tbl_Product 
    where [Code] = t.[Code] and [NameAttribute] = 'Size' and [ValueAttribute] = '10'
  )

See the demo
Edit:
To get the products that have all the attributes from Tbl_Atributes:

select distinct [Code], [Name] from Tbl_Product t
where
(select count(distinct [NameAttribute]) from Tbl_Product where [Code] = t.[Code]) =
(select count(distinct [NameAttribute]) from Tbl_Atributes)

See the demo
Edit2:
For better performance use a CTE:

with cte as (
  select count(distinct [NameAttribute]) total from Tbl_Atributes
)
select [Code], [Name] from Tbl_Product t
group by [Code], [Name]
having count(distinct [NameAttribute]) = (select total from cte)

See the demo
You can replace count(distinct [NameAttribute]) with count(*) if there is no case of duplicate attribute for a product.

Upvotes: 1

Related Questions