On Thu, 1999-09-09 18:48:03 +0200, Christian Mack wrote:
> Martin Ramsch wrote:
> > On Tue, 1999-08-31 15:49:24 -0400, Sean L. Gilley wrote:
> > > I need to query a table and select all rows that have duplicate
> > > values in two columns, and a specified value in a third.
> > >
> > > For example, I have a table:
> > >
> > > startdate datetime
> > > enddate datetime
> > > name text
> > >
> > > I need to know all rows in which row X startdate = row Y startdate,
> > > row X enddate = row Y enddate, row X name = 'ZZZ' and row Y name = 'ZZZ',
> > > where X and Y can be any row of the table.
> > >
> > > Is there any easy way to do this?
> > Yes. If you want to compare the rows of a table with rows of the same
> > table, you need to do a join of this table with itself. And to be
> > able to distinguish between both tables, you give both versions of the
> > table different alias names:
> > SELECT t1.*
> > FROM yourtable AS t1, yourtable AS t2
> > WHERE t1.startdate = t2.startdate
> > AND t1.enddate = t2.enddate
> > AND t1.name = 'ZZZ'
> > AND t2.name = 'ZZZ'
> > ;
> Aehmmm ...
> Your above query will give ALL rows from the table, not just the
> duplicates ;)
> Instead try something like this:
> , count(*) AS num
> name = 'Smith'
> GROUP BY
> , enddate
> num > 1
Christian, you're right that my example query is wrong, but
unfortunately yours doesn't do what Sean Gilley wants to, either. :)
Because--as I understood his question--he actually want's to see which
rows hold the duplicate fields.
Your query only shows one (random) row for each group of rows with the
same content in the 'duplicate' fields.
I see two solutions:
a) First find all combinations of values which are duplicates,
using your type of query.
And then for each of these combinations of values select
all matching rows.
I guess, this is the best way to go.
b) In my query I forgot the most important part ... :)
FROM yourtable AS t1, yourtable AS t2
WHERE t1.startdate = t2.startdate
AND t1.enddate = t2.enddate
AND t1.name = 'ZZZ'
AND t2.name = 'ZZZ'
GROUP BY ...all fields of t1's primary key...
I hope, this is correct now?
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26 E4 EC 80 58 7B 31 3A D7