Re: Basic SQL join question

Since we are dealing with a Set oriented system (ie DBs), it helps to
word the
problem in relevant terminology and then we see why you can not do
certain things
(simply)...

One uses words like union-of, subset-of, intersection-of, etc
The closest to what you state as 'concatenation' is 'union-of' and that
is why
you get something like

Test1=> select * from a, b, c;
id | id | id
----+----+----
a1 | b1 | c1
a1 | b1 | c2
a1 | b1 | c3
a1 | b1 | c4
a1 | b2 | c1
a1 | b2 | c2
a1 | b2 | c3
a1 | b2 | c4
a2 | b1 | c1
a2 | b1 | c2
a2 | b1 | c3
a2 | b1 | c4
a2 | b2 | c1
a2 | b2 | c2
a2 | b2 | c3
a2 | b2 | c4
a3 | b1 | c1
a3 | b1 | c2
a3 | b1 | c3
a3 | b1 | c4
a3 | b2 | c1
a3 | b2 | c2
a3 | b2 | c3
a3 | b2 | c4

If you say intersection-of, then join and those guys come in to give you
the shorter
resulting set....

Stephan Szabo wrote:

>On Fri, 31 Jan 2003, Jean-Christian Imbeault wrote:
>
>
>
>>Sorry for this simple question but I can't seem to get Postgres to do
>>what I want ...
>>
>>I want to get the concatenation of 2 or more tables with absolutely
>>nothing in common. How can I do this?
>>
>>For example
>>
>>Table a:
>>
>> a
>>-----
>> a1
>> a2
>> a3
>>
>>Table b:
>>
>> b
>>-----
>> b1
>> b2
>>
>>Table c:
>>
>> c
>>-----
>> c1
>> c2
>> c3
>> c4
>>
>>What is the proper SQL to return:
>>
>> a | b | c
>>---------------
>> a1 b1 c1
>> a2 b2 c2
>> a3 c3
>> c4
>>
>>
>>
>
>I can't think of a real SQL solution (although there might be
>one). A pl function could do this but it'd be a little wierd
>probably. Note that unless those tables are really selects with
>ordering the results are pretty indeterminate and probably
>meaningless since order is not guaranteed.
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>