Re: [SQL] ordered by join? ranked aggregate? how to?

[A. Kretschmer]

In response to wstrzalka :
> What I need is to join 2 tables
> 
> CREATE TABLE master(
> id INT4
> );
> 
> 
> CREATE TABLE slave (
> master_id INT4,
> rank INT4,
> value TEXT);
> 
> 
> What I need is to make the query:
> 
> SELECT m.id, array_agg(s.value) AS my_problematic_array
> FROM master AS m LEFT JOIN slave AS s ON (m.id = s.master_id)
> GROUP BY m.id;
> 
> return the 'my_problematic_array' in order specified by slave.rank
> 
> As you probably can guest I don't have any idea know how to do it :/

test=*# select * from master;
 id

  1
  2
(2 rows)

test=*# select * from slave;
 master_id | rank | value
---+--+---
 1 |5 | 5
 1 |3 | 3
 1 |7 | 7
(3 rows)

test=*# 
select id, array_agg(unnest) from (
  select id, unnest(my_problematic_array) from (
SELECT m.id, array_agg(s.value) AS my_problematic_array FROM master AS m 
LEFT JOIN slave AS s ON (m.id = s.master_id) GROUP BY m.id
  ) foo order by 1,2
) bar group by 1;
 id | array_agg
+---
  1 | {3,5,7}
  2 | {NULL}
(2 rows)


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] ordered by join? ranked aggregate? how to?

[A. Kretschmer]

In response to wstrzalka :
> What I need is to join 2 tables
> 
> CREATE TABLE master(
> id INT4
> );
> 
> 
> CREATE TABLE slave (
> master_id INT4,
> rank INT4,
> value TEXT);
> 
> 
> What I need is to make the query:
> 
> SELECT m.id, array_agg(s.value) AS my_problematic_array
> FROM master AS m LEFT JOIN slave AS s ON (m.id = s.master_id)
> GROUP BY m.id;

Faster solution (compared to my other email):

test=# select id, array_agg(value) from (SELECT m.id, s.value FROM
master AS m LEFT JOIN slave AS s ON (m.id = s.master_id) order by 1,2)
foo group by 1;
 id | array_agg
+---
  1 | {3,5,7}
  2 | {NULL}
(2 rows)

Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] CHECK constraint on multiple tables

[Rob Sargent]

Mario Splivalo wrote:

Tom Lane wrote:
  

Mario Splivalo  writes:


I have two tables, tableA and tableB:
CREATE TABLE tableA (idA integer primary key, email character varying
unique);
CREATE TABLE tableB (idB integer primary key, email character varying
unique);
  
Now, I want to create check constraint in both tables that would

disallow records to either table where email is 'mentioned' in other table.
  

Have you considered refactoring so there's only one table?



Unfortunately I can't do that, due to the
object-relational-mapper-wrapper-mambo-jumbo.

The only 'logical' idea that I can think of is separating emails to the
third table, and then use UNIQUE constraint on the email field on that
table, and then use FK constraint so that email fields in tables tableA
and tableB points to the email in the table emails.

Mario

  
Is that wrapper hibernate by any chance?  If so you might try adding a 
discriminator column to the single table TL suggested and map each class 
accordingly.


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql