Martin Ramsch wrote:
>
> 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:
> > SELECT
> > *
> > , count(*) AS num
> > FROM
> > yourtable
> > WHERE
> > name = 'Smith'
> > GROUP BY
> > startdate
> > , enddate
> > HAVING
> > 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 ... :)
> 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'
> GROUP BY ...all fields of t1's primary key...
> HAVING COUNT(*)>1;
>
> I hope, this is correct now?
>
> Regards,
> Martin
Hi Martin
Your b) has an typo :)
SELECT t2.* <----
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...
HAVING COUNT(*)>1;
Tschau
Christian
