Обсуждение: 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
Hi,
this is a good point to start.
inner join (
select id from table1
group by id
having count(*) > 1
) t1 on t2.id_table1 = t1.id
2011/1/5 Tarsis Lima <tarsis.lima@gmail.com>
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
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
SELECT<br /> <a href="http://t.id">t.id</a><br /> t.id_table1<br /> <a href="http://t.name">t.name</a><br />FROM<br/> Table2 t<br />INNER JOIN<br /> (SELECT <br /> t_inner.id_table1<br /> FROM<br /> Table2 t_inner<br/> GROUP BY 1<br /> HAVING COUNT(*) > 1) temp<br />ON temp.id_table1 = t.id_table1<br />;<br /><br /><divclass="gmail_quote">On Wed, Jan 5, 2011 at 8:18 AM, Tarsis Lima <span dir="ltr"><<a href="mailto:tarsis.lima@gmail.com"target="_blank">tarsis.lima@gmail.com</a>></span> wrote:<br /><blockquote class="gmail_quote"style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">how wouldthe SELECT to return only records with more than one<br /> occurrences of id_table1? example:<br /> -- Table1<br/> -- id --<br /> -------------------------<br /> 1<br /> 2<br /><br /><br /> -- Table2<br /> id --- |id_table1 | name<br /> ----------------------------------------------<br /> 4 ---- | ----- 1 ------ | Tom<br /> 5 ---- | ----- 1 ------ | Luci<br /> 6 ---- | ----- 2 ------ | Cleber ------>this can not return<br /><fontcolor="#888888"><br /><br /><br /><br /> --<br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org"target="_blank">pgsql-sql@postgresql.org</a>)<br /> To make changes to your subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br />