Basic SQL join question

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

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:

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