Databases
StackOverflow2013
Postsdbo
POTrouble with how to create select statement based on conditions
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTrouble with how to create select statement based on conditions
    text
    copied!<p>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:</p> <p>NOTE: simplifying the actual problem</p> <p>If I have two tables, each with a field I need to use EXAMPLE: </p> <pre><code>CUSTOMER.CUSTOMER_ID PRODUCT.PRODUCT_ID </code></pre> <p>I have 2 other tables that hold special product pricing based on these two fields EXAMPLE:</p> <pre><code>CUSTOMER_PRICING *************************************** * CUSTOMER_ID * PRODUCT_ID * PRICE * * * * ABC * 100 * 5.00 * *************************************** MARKET_PRICING *************************************** * CUSTOMER_ID * PRODUCT_ID * PRICE * * * * ACME * 200 * 7.00 * *************************************** </code></pre> <p>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 </p> <p>A) the PRICE field is not null</p> <p>B) and if a CUSTOMER_ID/PRODUCT_ID exists in both, I only want the record from CUSTOMER_PRICE</p> <p>Something like:</p> <pre><code>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)) </code></pre> <p>***then somehow do a join on these results.....</p> <p>Any Suggestions?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload