"Simple" SQL Select statement

[Jaymzie007]

Hi all, I'm after some help.
I have 2 tables,
T1 holds a product ID and a price
T2 holds a product ID and an amended price

I need a select statement that calls all ID’s and prices from T1 unless there is a product ID match with T2 when I need the amended price to be selected.

Eg.

T1
ID PRICE
1 12.75
2 34.75
3 22.30
4 48.99

T2
ID PRICE
1 10.99

I can’t select into a new table then update so I need to use Select to return the following

ID PRICE
1 10.99
2 34.75
3 22.30
4 48.99

I have tried all sorts, including joins, unions and case statements but this simple task has got the better of me. Any help gratefully received.

 

[dhulbert]

Select t1.id,
       case when t2.id is null then t1.price else t2.price
from   t1
       left outer join
       t2
       on
       t1.id = t2.id

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[Jaymzie007]

Wow, I only registered within the hour and I got a working response already.

Thank you very much dhulbert. It makes sense when you see it. I needed to add end to the case statement but it works like a dream.

Jaymzie

 

[r937]

SELECT t1.id
     , [blue]COALESCE(t2.price,t1.price)[/blue] AS price
  FROM t1
LEFT OUTER 
  JOIN t2  
    ON t2.id = t1.id

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon