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'
> ;
>
> In case joins are quite new to you, fisrt try
> SELECT * FROM yourtable AS t1, yourtable AS t2;
> to see exactly what happens.
>
> Regards,
> Martin
Hi Martin
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
;
Tschau
Christian
PS: Sorry for the late answer, I was really busy.
