Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id 10AA21337B5E
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri,  7 Jan 2011 07:05:15 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 84731-03-3 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Fri,  7 Jan 2011 11:05:07 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116])
	by mail.postgresql.org (Postfix) with ESMTP id DFA8E1337B96
	for <pgsql-sql@postgresql.org>; Fri,  7 Jan 2011 07:05:05 -0400 (AST)
Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by
	jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail
	Service Version 5.5.2653.13)
	id ZQ59C72F; Fri, 7 Jan 2011 11:04:07 -0000
Message-ID: <0FD1E8C83BCC4801952A4DEDE3E9713E@marktestcr.marktest.pt>
From: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>
To: "Tarsis Lima" <tarsis.lima@gmail.com>,
	<pgsql-sql@postgresql.org>
References: <d7cb6a09-b376-4e46-ba2b-3dfe82a90d77@c2g2000yqc.googlegroups.com>
Subject: Re: return records with more than one occurrences
Date: Fri, 7 Jan 2011 11:04:53 -0000
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
	reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-0.071 tagged_above=-5 required=5 tests=BAYES_05=-0.5,
	STOX_REPLY_TYPE=0.439, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201101/16
X-Sequence-Number: 34963

Howdy, Tarsis.

Please try this out.

SELECT a.id, id_table1,a.name
FROM "Table2" a
NATURAL JOIN 
(SELECT id_table1
"Table2" 
GROUP BY id_table1
HAVING COUNT(*) > 1) b

Tell me if it worked or not, and if it didn't the errors/uncorrect results.

Best,
Oliveiros

----- Original Message ----- 
From: "Tarsis Lima" <tarsis.lima@gmail.com>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, January 05, 2011 2:18 PM
Subject: [SQL] return records with more than one occurrences


> how would the SELECT to  return only records with more than one
> occurrences  of id_table1? example:
> -- Table1
> -- id --
> -------------------------
>    1
>    2
> 
> 
> -- Table2
>   id  --- | id_table1 | name
>  ----------------------------------------------
>   4  ---- | -----  1 ------ |   Tom
>   5  ---- | -----  1 ------ |   Luci
>   6  ---- | -----  2 ------ |  Cleber   ------>this can not return
> 
> 
> 
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql