How to union two tables, and make one "override" the other for similar records?

#1 (**permalink**) 12-27-2006, 11:01 PM

I have a list of prices for a stocks on each day in a table. I want to make a table that can override the price for a stock on a particular day. If XYZ is $1 on 1/1/2007 according to my main price table, I might want to substitute in $2 for that day instead in my override table. Then, I'd like to union the two, with the $2 record showing as the price for that day.

#2 (**permalink**) 01-04-2007, 01:22 AM

Found a solution:


SELECT MktCode,Date,Price 

FROM tblStockPrices AS t1

LEFT JOIN tblOverridePrices AS t2

ON t1.date=t2.date AND t1.mktcode=t2.mktcode

WHERE

T2.ID IS NULL

UNION

SELECT MktCode,Date,Price

FROM tblOverridePrices

#3 (**permalink**) 01-04-2007, 05:44 AM


SELECT

 DISTINCT t1.mktcode, t1.date, case when t2.mktcode IS NULL then t1.price else t2.price end

FROM

 tblstockprices t1

LEFT JOIN tbloverrideprices t2 ON t1.mktcode=t2.mktcode AND t1.date=t2.date

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

User Name		Remember Me?
Password

How to union two tables, and make one "override" the other for similar records?

ANSWER(S):