CODEWITHSUNDEEP
sqlsql-server-2008t-sql
pghtech
pghtech

Reputation: 3702

Trouble with how to create select statement based on conditions

Looking for some directions on what options I should be considering for the following problem of creating a select statement (SQL SERVER) based on conditions:

NOTE: simplifying the actual problem

If I have two tables, each with a field I need to use EXAMPLE: 

CUSTOMER.CUSTOMER_ID
PRODUCT.PRODUCT_ID

I have 2 other tables that hold special product pricing based on these two fields EXAMPLE:

CUSTOMER_PRICING 
***************************************
* CUSTOMER_ID  * PRODUCT_ID  * PRICE  *
*                                     *
*     ABC      *    100      *  5.00  *
***************************************

MARKET_PRICING 
***************************************
* CUSTOMER_ID  * PRODUCT_ID  * PRICE  *
*                                     *
*     ACME      *    200      *  7.00 *
***************************************

For every CUSTOMER.CUSTOMER_ID and PRODUCT.PRODUCT_ID I want to join CUSTOMER_PRICING and MARKET_PRICING records using a left outer join. But the conditions are only if

A) the PRICE field is not null

B) and if a CUSTOMER_ID/PRODUCT_ID exists in both, I only want the record from CUSTOMER_PRICE

Something like:

IF EXISTS (SELECT C.CUSTOMER_ID, P.PRODUCT_ID FROM CUSTOMER C, PRODUCT P 
           WHERE C.CUSTOMER_ID, P.PRODUCT_ID IN 
           (SELECT PRICE FROM CUSTOMER_PRICE WHERE PRICE IS NOT NULL))
ELSE IF EXISTS 
          (SELECT C.CUSTOMER_ID, P.PRODUCT_ID FROM CUSTOMER C, PRODUCT P 
           WHERE C.CUSTOMER_ID, P.PRODUCT_ID IN 
           (SELECT PRICE FROM MARKET_PRICE WHERE PRICE IS NOT NULL))

***then somehow do a join on these results.....

Any Suggestions?

Upvotes: 1

Views: 417

Answers (4)

Michael Riley - AKA Gunny
Michael Riley - AKA Gunny

Reputation: 5184

Creating a view called CMPricing would greatly simplify this situation.

CREATE VIEW CMPricing
AS

SELECT
t1.CUSTOMER_ID,
t1.PRODUCT_ID,
t1.PRICE AS CUSTOMER_PRICE
t2.PRICE AS MARKET_PRICE

FROM CUSTOMER_PRICING t1
INNER JOIN MARKET_PRICING t2
ON t1.CUSTOMER_ID = t2.CUSTOMER_ID
AND t1.PRODUCT_ID = t2.PRODUCT_ID

WHERE t1.PRICE IS NOT NULL 
AND t2.PRICE IS NOT NULL
 Then you query this view to get what you want.

This view will contain only the data that is common between both "PRICE" tables where both "PRICE" values are not null. This view in essence does the filtering for you which in turn makes your queries with the other data tables easier to do and understand.

Upvotes: 1

arb
arb

Reputation: 7863

I think I've come up with a solution. I'm sure a SQL expert could come up with something better, but this should get you moving:

WITH CTE AS
(
    SELECT C.CUSTOMER_ID, C.PRODUCT_ID, C.CUSTOMER_PRICE as [PRICE], 1 as [T]
    FROM CUSTOMER_PRICE C WHERE C.CUSTOMER_PRICE IS NOT NULL
    UNION
    SELECT P.CUSTOMER_ID, P.PRODUCT_ID,P.MARKET_PRICE as [PRICE], 2 as [T]
    FROM MARKET_PRICE P WHERE P.MARKET_PRICE IS NOT NULL
)
select CUSTOMER_ID,PRODUCT_ID,
    (
        SELECT TOP (1) PRICE FROM CTE i WHERE i.CUSTOMER_ID = o.CUSTOMER_ID AND i.PRODUCT_ID = o.PRODUCT_ID
        ORDER BY T
    ) as [PRICE]
from CTE o
GROUP BY CUSTOMER_ID,PRODUCT_ID

That will give you a distinct list of Customer IDs, Product IDs, and Prices; favoring the CUSTOMER_PRICE price.

EDIT This will only work with SQL databases 2008 and up. It makes use of Common Table Expressions which are new constructs to 2008.

UPDATE Fixed where clause.

Upvotes: 1

Andriy M
Andriy M

Reputation: 77737

You could join the cross-product of CUSTOMER and PRODUCT with both CUSTOMER_PRICING and MARKET_PRICING, filtering out the rows where both CUSTOMER_PRICING.PRICE and MARKET_PRICING.PRICE are NULL. It might look something like this:

SELECT
  c.CUSTOMER_ID,
  p.PRODUCT_ID,
  COALESCE(cp.PRICE, mp.PRICE) AS PRICE
FROM CUSTOMER c
  CROSS JOIN PRODUCT p
  LEFT JOIN CUSTOMER_PRICING cp ON cp.CUSTOMER_ID = c.CUSTOMER_ID AND cp.PRODUCT_ID = p.PRODUCT_ID
  LEFT JOIN MARKET_PRICING   mp ON mp.CUSTOMER_ID = c.CUSTOMER_ID AND mp.PRODUCT_ID = p.PRODUCT_ID
WHERE cp.PRICE IS NOT NULL OR mp.PRICE IS NOT NULL
-- Or, put differently:
-- WHERE NOT (cp.PRICE IS NULL AND mp.PRICE IS NULL)

Upvotes: 1

Coder
Coder

Reputation: 896

If you are using ORACLE, you should use union to eliminate the duplicate rows and make 2 separate queries.

Upvotes: 0

Related Questions