Re: SQL-question (JOIN)

on 1/31/03 2:20 PM, pilsl(at)goldfisch(dot)at purportedly said:

> 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.

SELECT t1.uid,
CASE WHEN t1.uid = t2.uid THEN t2.name ELSE t1.name END AS result_name
FROM t1 LEFT OUTER JOIN t2 USING (uid) ORDER BY t1.uid;

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"