Thread: Basic SQL join question
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
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!
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
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.
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
>
>
>
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 > >
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
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/ !
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