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