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