CODEWITHSUNDEEP
sqlsql-server
shikarishambu
shikarishambu

Reputation: 2269

Optional where clause

I have two table

Table A - Product information - color, size etc...(product_id, color, size...)

Table B - Rate information - rate might vary by color, size, state etc... (rate_id, product_id, color, size, state, rate)

Not all products have rates that vary by color, size, state etc...This changes by product

If that is the case only attributes by which rates vary have a value in the rate table e.g. if Product X rate does not vary by size or state but varies by color there will entries in the rate table with different color values but the size, state field will be null.

Is there a way in SQL to get the right rate value for a product - I cannot match on all product attributes because there might not be a matching row

What I want is a query that gets the rate associated with Product X and will work with for other products whose rates may vary by different set of attributes.

Upvotes: 1

Views: 1110

Answers (4)

hythlodayr
hythlodayr

Reputation: 2387

Have you tried an outer join?

SELECT
   P.*,
   R.*
FROM 
   PRODUCT P LEFT OUTER JOIN RATE R 
   ON (P.product_ID = R.product_id AND (
       P.COLOR = R.COLOR
    OR P.SIZE  = R.SIZE
   ))
WHERE
   P.product_id = ?

Edit: Left off the WHERE clause.

Upvotes: 0

btilly
btilly

Reputation: 46389

You can do this with static SQL with the trick ISNULL(product.color, 'n/a') = ISNULL(rate.color, ISNULL(product.color, 'n/a')).

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294207

Everything you want to know about the subject, and more: Dynamic Search Conditions in T-SQL.

Upvotes: 3

Phil Murray
Phil Murray

Reputation: 6554

This is how I would implement it

http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

And a very good explaination here http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Phil

Upvotes: 2

Related Questions