Re: Basic SQL join question

Hi,

If you had an id column you could get the result that you need.
If I knew how to get get the equivalent of  oralce row id from
postgresql then may be the ID column would not be needed.

This may not be the best way, but i could get it to work by pivoting off
a view of  IDs.


Create the view of all IDs

create view v_abc as select id from a union select id from b union
select id from c;

Then use left join on in your query.

select a,b,c from v_abc
left join a on v_abc.id = a.id
left join c on v_abc.id = c.id
left join b on v_abc.id = b.id;

 a  | b  | c
----+----+----
 a1 | b1 | c1
 a2 | b2 | c2
 a3 |    | c3
    |    | c4
(4 rows)

Regards,
Simon

PS - you could post your join query in a view.
     - view, stored procedures etc... is why i do not use mysql.


Example table data.

Table a:

 id | a
----+----
  1 | a1
  2 | a2
  3 | a3

Table b:

 id | b
----+----
  1 | b1
  2 | b2


Table c:

 id | c
----+----
  1 | c1
  2 | c2
  3 | c3
  4 | c4



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