[SQL] Sorting data based fields in two linked tables
Hi, I am looking for a way to sort data returned from two tables with the first sort based on a field from table A and the secord sort based on the results of the first sort but the sort field is from table B. While I can sort on either fields from either table, I cannot get it to work on both. I have tried a crosstab query, but this a fails as the number of rows returned from TABLE B for each row in TABLE A is an unknown. I tried creating a temporary table, to then sort on, but this failed as well. Example layout below: Table A ID FIELD1 FIELD2 Table B ID, A.ID FIELD1,FIELD2 Output Based on sorting A.FIELD2, then B.FIELD1 A.ID,A.FIELD1,A.FIELD2, B.FIELD1,B.FIELD2, B.FIELD1,B.FIELD2, B.FIELD1,B.FIELD2 Can anyone help me with this? Regards -- 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] Sorting data based fields in two linked tables
Can you provide An example? Best, Oliveiros Enviado via iPhone Em 13/05/2011, às 04:00 PM, "R. Smith" escreveu: > Hi, > > I am looking for a way to sort data returned from two tables with the > first sort based on a field from table A and the secord sort based on > the results of the first sort but the sort field is from table B. > While I can sort on either fields from either table, I cannot get it > to work on both. I have tried a crosstab query, but this a fails as > the number of rows returned from TABLE B for each row in TABLE A is an > unknown. I tried creating a temporary table, to then sort on, but this > failed as well. Example layout below: > > Table A > > ID FIELD1 FIELD2 > > Table B > > ID, A.ID FIELD1,FIELD2 > > Output Based on sorting A.FIELD2, then B.FIELD1 > > A.ID,A.FIELD1,A.FIELD2, B.FIELD1,B.FIELD2, B.FIELD1,B.FIELD2, > B.FIELD1,B.FIELD2 > > Can anyone help me with this? > > Regards > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: [SQL] Sorting data based fields in two linked tables
SELECT A.ID,A.FIELD1,A.FIELD2, B.FIELD1,B.FIELD2, B.FIELD1,B.FIELD2, B.FIELD1,B.FIELD2 FROM a INNER JOIN B ON a.id = b.a_id ORDER BY a.field2 ASC, b.field1 ASC ; - Reply message - From: "R. Smith" Date: Fri, May 13, 2011 12:00 pm Subject: [SQL] Sorting data based fields in two linked tables To: Hi, I am looking for a way to sort data returned from two tables with the first sort based on a field from table A and the secord sort based on the results of the first sort but the sort field is from table B. While I can sort on either fields from either table, I cannot get it to work on both. I have tried a crosstab query, but this a fails as the number of rows returned from TABLE B for each row in TABLE A is an unknown. I tried creating a temporary table, to then sort on, but this failed as well. Example layout below: Table A ID FIELD1 FIELD2 Table B ID, A.ID FIELD1,FIELD2 Output Based on sorting A.FIELD2, then B.FIELD1 A.ID,A.FIELD1,A.FIELD2, B.FIELD1,B.FIELD2, B.FIELD1,B.FIELD2, B.FIELD1,B.FIELD2 Can anyone help me with this? Regards -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] self join
Hi, This probably reflects my confusion with how self joins work. Suppose we have this table: =# SELECT * FROM tmp; a | b ---+--- 1 | 2 2 | 3 4 | 5 (3 rows) If I want to get a table with records where none of the values in column b are found in column a, I thought this should do it: =# SELECT * FROM tmp t1, tmp t2 WHERE t2.b <> t1.a; a | b | a | b ---+---+---+--- 1 | 2 | 1 | 2 1 | 2 | 2 | 3 1 | 2 | 4 | 5 2 | 3 | 2 | 3 2 | 3 | 4 | 5 4 | 5 | 1 | 2 4 | 5 | 2 | 3 4 | 5 | 4 | 5 (8 rows) I need to get: a | b | a | b ---+---+---+--- 1 | 2 | 1 | 2 4 | 5 | 4 | 5 Or just: a | b ---+--- 1 | 2 4 | 5 -- Seb -- 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] self join
Hi 2011/5/15 Seb : > Hi, > > This probably reflects my confusion with how self joins work. > > Suppose we have this table: > > =# SELECT * FROM tmp; > a | b > ---+--- > 1 | 2 > 2 | 3 > 4 | 5 > (3 rows) > > If I want to get a table with records where none of the values in column > b are found in column a, I thought this should do it: > > =# SELECT * FROM tmp t1, tmp t2 WHERE t2.b <> t1.a; > a | b | a | b > ---+---+---+--- > 1 | 2 | 1 | 2 > 1 | 2 | 2 | 3 > 1 | 2 | 4 | 5 > 2 | 3 | 2 | 3 > 2 | 3 | 4 | 5 > 4 | 5 | 1 | 2 > 4 | 5 | 2 | 3 > 4 | 5 | 4 | 5 > (8 rows) > > I need to get: > > a | b | a | b > ---+---+---+--- > 1 | 2 | 1 | 2 > 4 | 5 | 4 | 5 > > Or just: > > a | b > ---+--- > 1 | 2 > 4 | 5 Your query doesn't have an explicit join and is producing a cartesian result. I don't think a self- join will work here; a subquery should produce the result you're after: SELECT * FROM tmp t1 WHERE NOT EXISTS(SELECT TRUE FROM tmp t2 WHERE t2.b=t1.a); HTH Ian Lawrence Barwick -- 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] self join
On Sun, 15 May 2011 07:39:06 +0900, Ian Lawrence Barwick wrote: [...] > Your query doesn't have an explicit join and is producing a cartesian > result. > I don't think a self- join will work here; a subquery should produce > the result you're after: > SELECT * FROM tmp t1 WHERE NOT EXISTS(SELECT TRUE FROM tmp t2 WHERE > t2.b=t1.a); This produces exactly the result I'm after. I'll need to understand the EXISTS statement there in more detail. Thanks! -- Seb -- 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] self join
On 2011-05-14, Seb wrote: > Hi, > > This probably reflects my confusion with how self joins work. > > Suppose we have this table: > If I want to get a table with records where none of the values in column > b are found in column a, I thought this should do it: use the "NOT IN" operator with a subquery to retch the disallowed values. select * from tmp where a NOT IN (select b from tmp); -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
