List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:September 9 1999 4:48pm
Subject:Re: query help..
View as plain text  
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.

Thread
query help..Sean L. Gilley31 Aug
  • Re: query help..Martin Ramsch1 Sep
  • Re: query help..Christian Mack9 Sep
    • Re: query help..Martin Ramsch9 Sep
  • Re: query help..Christian Mack13 Sep