Different results on the basis of different foreign key value using a falg in where clause

Question

Please see attached image.

Can you please tell me what query will work. Please ignore isdefinite and productpriceid columns.

Thanks

Answer 1

If you want a single query, this should do it if I've interpreted your question properly:

SELECT  DISTINCT t1.SupplierVenueProductID, [...]
FROM    table t1
        LEFT JOIN
                table t2
                ON t1.SupplierVenueProductID = t2.SupplierVenueProductID
                AND t2.iscustomcost = 1
WHERE   t2.SupplierVenueProductID IS NULL
OR      t1.iscustomcost = 1

I don't know your table name, but you join it to itself.

Answer 2

I'm a bit lost on what you want to accomplish here, going by your requirement if isCustomCost = 1 then return record #3 from SupplierVenueProductId 1 and both records from SupplierVenueProductId 2

Trying to generalize this, I think what you need is :

return all rows from the table, unless when there is a record for a SupplierVenueProductId that has isCustomCost = 1, then only return that record for this SupplierVenueProductId

Which then becomes something along the lines of :

SELECT t1.* 
  FROM myTable t1
 WHERE t1.isCustomCost = 1
    OR NOT EXISTs (SELECT *
                     FROM t2
                    WHERE t2.SupplierVenueProductId = t1.SupplierVenueProductId
                      AND t2.isCustomCost = 1)

Hope this helps.