Re: Basic SQL join question

Folks,
There are actually a couple of ways you could do this, both probably aren't
worth while.
The first one would be:
SELECT (SELECT Field FROM A LIMIT 1 OFFSET 1) AS A, (SELECT Field FROM B
LIMIT 1 OFFSET 1) AS B, (SELECT Field FROM C LIMIT 1 OFFSET 1) AS C
UNION SELECT (SELECT Field FROM A LIMIT 1 OFFSET 2) AS A, (SELECT Field FROM
B LIMIT 1 OFFSET 2) AS B, (SELECT Field FROM C LIMIT 1 OFFSET 2) AS C
...

The second would be to make a function that did something like (This isn't
of course real code):

set variable to result of SELECT MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM
A UNION SELECT COUNT(*) AS cnt FROM B UNION SELECT COUNT(*) AS cnt FROM C)
Table;

for counter (1 .. varable)
INSERT INTO TEMP table SELECT (SELECT Field FROM A LIMIT 1 OFFSET counter)
AS A, (SELECT Field FROM B LIMIT 1 OFFSET counter) AS B, (SELECT Field FROM
C LIMIT 1 OFFSET counter) AS C

return result of SELECT * FROM table;

Like I said, kinda grotesque, but it would work.

Thanks,
Peter Darley

-----Original Message----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Michael Meskes
Sent: Thursday, January 30, 2003 11:00 PM
To: Jean-Christian Imbeault
Cc: PostgreSQL-general
Subject: Re: [GENERAL] Basic SQL join question

On Fri, Jan 31, 2003 at 12:08:24PM +0900, Jean-Christian Imbeault wrote:
> For example
> ...
> What is the proper SQL to return:
>
> a | b | c
> ---------------
> a1 b1 c1
> a2 b2 c2
> a3 c3
> c4

None. Even in theory this is not possible. How shall the database system
know that a1,b1,c1 belong together? You said the tables have absolutely
nothing in common. Keep in mind that SQL works on sets, not on single
values.

Michael
--
Michael Meskes
Email: Michael(at)Fam-Meskes(dot)De
ICQ: 179140304
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html