0

I'll try to explain what I want. and if you can suggest me a better title of question it would be great too.

enter image description here

In my stored procedure, I am passing a user-defined table that contains AttributeId and AttributeValueId, and I want to retrieve MappedAttributeId of the basis of that user-defined table

e.g I want to select value MappedAttributeId = 99 because in the user-defined table I pass

AttributeId    AttributeValueId
------------------------------
    84             156
    85             158

I use join between table that i attached above with my user-defined table but it will return me multiple results for all attributeid 84 where AttributeValueId is 156 and for all attributeid 85 where AttributeValue is 156

But I want to the MappedAttributeId where AttributeId = 84 AttributeValueId = 156 and AttributeId = 85 AttributeValueId = 158

I want to use MappedAttributeId in outer query and i'm using join in subquery so is there anyway to write this query without using cursor.

Ok here is the user-defined table that I passed to the stored procedure:

declare @p4 [Order].ProductSelectedAttributes
insert into @p4 values(84,156)
insert into @p4 values(85,158)

The first value in above parameter is AttributeId and the second one is AttributeValueId

The query I wrote is

Select MappedAttributeId 
from [Product].tblMappedAttributesDetail mad -- This is the table in the first picture on top
join @p4 psa on psa.attrId = mad.AttributeId and psa.attrValId = mad.AttributeValueId

This query is returning three values which are 99,99,100 because the first row in my parameter (84,156) match the first row in the table where MappedAttributeId is 99 and the third row where MappedAttributeId is 100, and the 2nd row in my parameter (85,158) match the 2nd row in the table where MappedAttributeId is 100. I can remove the repeating 99 value using GroupBy but how to exclude 100 from the result?

but I just want MappedAttributeId 99 because it matched both rows of my parameter (84,156) and (85,158). I don't want MappedAttributeId 100 because it only matches (85,158) and not the other row (84,156)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Zubair Khakwani
  • 328
  • 5
  • 18
  • Edit your question and be clear about the results that you want. – Gordon Linoff Apr 24 '21 at 13:08
  • Isn't what you're after just a `JOIN`? – Thom A Apr 24 '21 at 13:26
  • Does this answer your question? [Select group of rows that match all items in a list](https://stackoverflow.com/questions/15977126/select-group-of-rows-that-match-all-items-in-a-list) – Serg Apr 24 '21 at 13:52
  • @GordonLinoff I want to select MappedAttributeId from the picture where ( AttributeId and AttributeValueId matched all items in a user-defined table ) e.g you can see MappedAttributeId 99 is twice in the table but AttributeId and AttributeValueId is different in each row now I want to select 99 from MappedAttributeId when my user-defined table contains 2 rows with values 84,156 and 85,158 – Zubair Khakwani Apr 24 '21 at 14:49
  • @Serg it's a little different. you can read the comment i just added it may explain you what i want – Zubair Khakwani Apr 24 '21 at 14:51
  • Your description is rather ambiguous and needs you to actually include the query you are talking about so we can see how to improve or refactor it. – Stu Apr 24 '21 at 19:30
  • Please [edit] and add the query. It sounds like you just want a join, if not please clarify – Charlieface Apr 24 '21 at 22:13
  • @Stu check now I added the parameter I passed and the query I write and also mentioned what is the result I want and why. – Zubair Khakwani Apr 25 '21 at 00:16
  • @Charlieface check now I added the parameter I passed and the query I write and also mentioned what is the result I want and why. – Zubair Khakwani Apr 25 '21 at 00:16
  • @Stu Thank you all for the help, I found the answer you can check it and you can suggest me a better title for the question so if anyone else can found the solution easily. – Zubair Khakwani Apr 25 '21 at 00:50
  • @Charlieface Thank you all for the help, I found the answer you can check it and you can suggest me a better title for the question so if anyone else can found the solution easily. – Zubair Khakwani Apr 25 '21 at 00:50

1 Answers1

0

I just added group by and use Having for the count and it worked. Thanks to all for trying to help.

Select MappedAttributeId
from [Product].tblMappedAttributesDetail mad
join @p4 psa on psa.attrId = mad.AttributeId and psa.attrValId = mad.AttributeValueId
group by MappedAttributeId
Having (Select Count(1) from @p4) = Count(1)
Zubair Khakwani
  • 328
  • 5
  • 18