Horton

Thread

  1. Basic SQL join question

    Jean-Christian Imbeault <jc@mega-bucks.co.jp> — 2003-01-31T03:08:24Z

    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


Thanks,

Jc

  2. Re: Basic SQL join question

    codeWarrior <gpatnude@adelphia.net> — 2003-01-31T05:15:41Z

    Here's an example using aggregates that's sort of close...

SELECT (SELECT COUNT(id) FROM cb_person_plan_enroll WHERE person_id = 72) AS
STDPLANS, (SELECT COUNT(id) FROM cb_person_pog_enroll WHERE person_id = 72)
AS POGPLANS, (SELECT COUNT(id) FROM cb_person_grp_enroll WHERE person_id =
72) AS GRPPLANS;

The problem is that if you dont need an aggregate and the tables nothing in
common to join on... you really can't go around joining them if there's
nothing to join....

Now -- if you were to create a cursor and select into it from the tables in
question... you might get somewhere...



"Jean-Christian Imbeault" <jc@mega-bucks.co.jp> wrote in message
news:3E39E8A8.7020001@mega-bucks.co.jp...
> 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
>
>
> Thanks,
>
> Jc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

  3. Re: Basic SQL join question

    Michael Meskes <meskes@postgresql.org> — 2003-01-31T07:00:11Z

    On Fri, Jan 31, 2003 at 12:08:24PM +0900, Jean-Christian Imbeault wrote:
> For example
> ...
> What is the proper SQL to return:
> 
>   a |  b |  c
> ---------------
>  a1   b1   c1
>  a2   b2   c2
>  a3        c3
>            c4

None. Even in theory this is not possible. How shall the database system
know that a1,b1,c1 belong together? You said the tables have absolutely
nothing in common. Keep in mind that SQL works on sets, not on single
values.

Michael
-- 
Michael Meskes
Email: Michael@Fam-Meskes.De
ICQ: 179140304
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

  4. Re: Basic SQL join question

    Arjen van der Meijden <acm@tweakers.net> — 2003-01-31T09:16:24Z

    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?
You can't, or at least you shouldn't.
If you want to display them inline in your application, try building 
some clientside code to display het pretty.

But since there is no relation, the database will never be able to 
understand how to put the data together.

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

As far as I know there is no SQL to return that :)

Regards,

Arjen

  5. Re: Basic SQL join question

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-01-31T14:53:22Z

    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.

  6. Re: Basic SQL join question

    Simon Mitchell <pgsql@jseb.com> — 2003-01-31T20:14:46Z

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

  7. Re: Basic SQL join question

    Medi Montaseri <medi.montaseri@intransa.com> — 2003-01-31T22:12:46Z

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

  8. Re: Basic SQL join question

    Will Trillich <will@serensoft.com> — 2003-02-06T16:50:25Z

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

  9. Re: Basic SQL join question

    Peter Darley <pdarley@kinesis-cem.com> — 2003-02-06T18:51:23Z

    Folks,
	There are actually a couple of ways you could do this, both probably aren't
worth while.
	The first one would be:
SELECT (SELECT Field FROM A LIMIT 1 OFFSET 1) AS A, (SELECT Field FROM B
LIMIT 1 OFFSET 1) AS B, (SELECT Field FROM C LIMIT 1 OFFSET 1) AS C
UNION SELECT (SELECT Field FROM A LIMIT 1 OFFSET 2) AS A, (SELECT Field FROM
B LIMIT 1 OFFSET 2) AS B, (SELECT Field FROM C LIMIT 1 OFFSET 2) AS C
...

	The second would be to make a function that did something like (This isn't
of course real code):

set variable to result of SELECT MAX(cnt) FROM (SELECT COUNT(*) AS cnt FROM
A UNION SELECT COUNT(*) AS cnt FROM B UNION SELECT COUNT(*) AS cnt FROM C)
Table;

for counter (1 .. varable)
INSERT INTO TEMP table SELECT (SELECT Field FROM A LIMIT 1 OFFSET counter)
AS A, (SELECT Field FROM B LIMIT 1 OFFSET counter) AS B, (SELECT Field FROM
C LIMIT 1 OFFSET counter) AS C

return result of SELECT * FROM table;

	Like I said, kinda grotesque, but it would work.

Thanks,
Peter Darley

-----Original Message----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Michael Meskes
Sent: Thursday, January 30, 2003 11:00 PM
To: Jean-Christian Imbeault
Cc: PostgreSQL-general
Subject: Re: [GENERAL] Basic SQL join question


On Fri, Jan 31, 2003 at 12:08:24PM +0900, Jean-Christian Imbeault wrote:
> For example
> ...
> What is the proper SQL to return:
>
>   a |  b |  c
> ---------------
>  a1   b1   c1
>  a2   b2   c2
>  a3        c3
>            c4

None. Even in theory this is not possible. How shall the database system
know that a1,b1,c1 belong together? You said the tables have absolutely
nothing in common. Keep in mind that SQL works on sets, not on single
values.

Michael
--
Michael Meskes
Email: Michael@Fam-Meskes.De
ICQ: 179140304
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html