Re: Basic SQL join question

On Sat, Feb 01, 2003 at 07:14:46AM +1100, Simon Mitchell wrote:
> 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

now THAT's cool.

how about having a "parent"-ish table listed with all its
"subset" records in one row?

the one-sub-per-line "select" is trivial:

Thompson website
Andrews exim
Andrews quotas
Andrews sql
Peterson quotas
Peterson website

but this probably isn't:

person.lname | project1 | project2 | project3
--------------+----------+----------+----------
Thompson | website | |
Andrews | exim | quotas | sql
Peterson | quotas | website |

is that kind of thing possible? even if you limit your subsets
to the first three?

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !