sql - Connect tables with or without records -

i have 2 tables units , price. units table fields unit1, unit2. price table fields id, rateper, price. want connect 2 tables price table rateper <=0 or price table empty return unit1 else rateper. wrote query below not wroking

 select case when rateper <=0  unit1 else rateper units,price 

am using postgresql version 9.0

units table

+------+-----+ |unit1 |unit2| -------------- | 2    | 10  | | 1    | 20  | +------+------ 

price table

+------+-------------+---------+ |id    + rate per    + price   | -------------------------------- |1     |0            | 100     | |2     |1            | 200     | |3     |2            | 300     | --------------------------------  result :  2 1 3 

if price table not have rows show result

2 1 

you have specify columns on want join these tables

select     case         when p.rateper <= 0 or p.rateper null u.unit1         else p.rateper     end units u     full outer join price p on p.id = u.??? 

update think need kind of full outer join. i'm still doesn't understand want, try query:

select     coalesce(u."unit1", p."id") units u     full outer join (select * price "rate per" > 0) p on p."id" = u."unit1"; 

sql fiddle demo