Обсуждение: SQL-question (JOIN)

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.

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.

thnx,
peter


--
mag. peter pilsl
IT-Consulting
tel: +43-699-1-3574035
fax: +43-699-4-3574035
pilsl@goldfisch.at
http://www.goldfisch.at

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.

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

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