CODEWITHSUNDEEP
sqlsql-server
ahmad noori
ahmad noori

Reputation: 127

having an and condition on multiple rows - sql server

i have the following query where i'm trying to select one value based on a condition

select distinct tblItemTypePropValue.ItemType_Fkey, tblItemPrice.price, dbo.fnGetDiscountItemType(tblItemTypePropValue.ItemType_Fkey) as finalPrice 
from tblItemTypePropValue
inner join tblItemPrice
on tblItemTypePropValue.ItemType_Fkey = tblItemPrice.itemType_fkey
where Value_Fkey in (2097,2131)
and tblitemtypepropvalue.ItemType_Fkey in (select id from tblItemType where Item_Fkey = 12241)

the problem is i n the where in value_fkey in (2097,2131)

is there a way to write an and condition on multiple rows where value_fkey = 2097 and = 2131.

and also, can it be done without a join since my parameters inside the in can vary in count.

sample result

itemtype_fkey   price finalPrice
6191            100       93

so the query will only return the ItemType_Fkey that has both Value_Fkeys specified in the query

Value thanks,

Upvotes: 0

Views: 218

Answers (3)

Marc Guillot
Marc Guillot

Reputation: 6455

You can do two INNER JOINs and check for the existence of the 2097 and 2131 keys on them.

Being INNER JOINS, only the Prices having both of those keys will be returned.

select distinct P.ItemType_Fkey, P.price, 
                dbo.fnGetDiscountItemType(P.ItemType_Fkey) as finalPrice 
from tblItemType T
     inner join tblItemPrice P on P.ItemType_Fkey = T.id
     inner join tblItemTypePropValue V1 on V1.ItemType_Fkey = P.ItemType_Fkey and V1.Value_Fkey = 2097 
     inner join tblItemTypePropValue V2 on V2.ItemType_Fkey = P.ItemType_Fkey and V2.Value_Fkey = 2104 
where T.Item_Fkey = 12241

Upvotes: 1

dnoeth
dnoeth

Reputation: 60472

You probably want those items where rows for both Value_Fkey exist like this

SELECT 
    v.ItemType_Fkey,
    max(p.price), -- max/min/avg?
    dbo.fnGetDiscountItemType(v.ItemType_Fkey) AS finalPrice
FROM tblItemTypePropValue AS v
INNER JOIN tblItemPrice AS p
ON v.ItemType_Fkey = p.itemType_fkey
WHERE Value_Fkey IN (2097,2131) --- list of values
  AND v.ItemType_Fkey
  IN ( SELECT id
       FROM tblItemType
       WHERE Item_Fkey = 12241
     )
GROUP BY v.ItemType_Fkey
HAVING COUNT(DISTINCT Value_Fkey) = 2 -- number of values in list

Upvotes: 0

fiverbox.com
fiverbox.com

Reputation: 125

select distinct tblItemTypePropValue.Value_Fkey,tblItemTypePropValue.ItemType_Fkey, tblItemPrice.price, dbo.fnGetDiscountItemType(tblItemTypePropValue.ItemType_Fkey) as finalPrice 
from tblItemTypePropValue
inner join tblItemPrice
on tblItemTypePropValue.ItemType_Fkey = tblItemPrice.itemType_fkey
where ((Value_Fkey = 2097) or (Value_Fkey = 2131))
and tblitemtypepropvalue.ItemType_Fkey in (select id from tblItemType where Item_Fkey = 12241)

Upvotes: 0

Related Questions