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:
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.
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