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

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