SQL-question (JOIN)

On Fri, 31 Jan 2003 pilsl@goldfisch.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.

pilsl@goldfisch.at writes:

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

select uid, coalesce(table2.name, table1.name) as name from table1 left
join table2 using (uid);

--
Peter Eisentraut peter_e@gmx.net

On Fri, Jan 31, 2003 at 22:20:52 +0100,
pilsl@goldfisch.at wrote:

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

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.

I think you want something like this:
select coalesce(table2.name,table1.name) from table1 right join table2
using (uid);

(Warning the above wasn't actually tested for syntax errors. It also assumes
that name is not null in table2.)

on 1/31/03 2:20 PM, pilsl@goldfisch.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"