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@serensoft.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/ !