Can we run subquery multiple time for outer query

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

In My Store 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 Store porcedure

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 and the query I write 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)

https://stackoverflow.com/questions/67243121/can-we-run-subquery-multiple-time-for-outer-query April 24, 2021 at 09:07PM