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