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