Re: SQL-question (JOIN)

On Fri, 31 Jan 2003 pilsl(at)goldfisch(dot)at wrote:

> not sure if such question are on-topic here. (where would this
> question be on-topic ?)

pgsql-sql would be better in general.

> I need to join two tables with a logical "if-statement". If for a
> certain row in table1 there is a related row in table2, then take the
> row from table2 else take it from table1. The relation is a simple
> equal on one column.
>
> example:
>
> table1:
> uid | name
> ----+-----
> 1 | bob
> 2 | jim
> 3 | tom
>
> table2:
> uid | name
> ----+-----
> 2 | frank
>
>
> the final join should return:
> uid | name
> ----+-----
> 1 | bob
> 2 | frank
> 3 | tom
>
> I played around with joins and intersects and distincts but only ended
> up in complex unperformant queries that didnt do what I intended. I
> miss the basic idea how to solve this.

Well, you could probably do something like for this particular case:
select uid, coalesce(table2.name, table1.name) from table1 left outer
join table2 using (uid);

In the left outer join, the rows without matching table2 rows effectively
get a NULL for the table2.name so coalesce will do what you want.