Skip site navigation (1) Skip section navigation (2)

Re: SQL-question (JOIN)

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: <pilsl(at)goldfisch(dot)at>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: SQL-question (JOIN)
Date: 2003-01-31 23:41:11
Message-ID: 20030131153546.R51807-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-general
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.


In response to

pgsql-general by date

Next:From: Williams, Travis L, NPONSDate: 2003-01-31 23:47:47
Subject: Re: grant to all tables
Previous:From: will trillichDate: 2003-01-31 23:26:56
Subject: Re: Can you use array data types w/ the COPY command?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group