Comparing table with itself

[nodge87]

Hi,

I am trying to select certain rows from a table based on criteria relating to other rows in the same table. I want each row in the table to be compared with each other row and if the term dates of the selected row are within those of any of the other rows then I wish for that selected row to be put into the result set in for the query.

(Hope that makes sence!)

However, what it is doing is it is comparing the selected row to itself and It is automatically putting each row into the result set as each row fullfills the criteria when being tested against itself.....

What I want to happen is that once the query selects a row from the table BTI, it then checks it against each other row in the table (Which is duplicated and called TEST) without comparing that row to itself.

The code I am trying to use at the moment is as follows....:

SELECT DISTINCT BTI.*
FROM BTI, BTI as [TEST]
WHERE BTI.[Term End] Between [TEST].[Term Beg] And [TEST].[Term End];

Hope someone can help! Thanks!!

[SDas]

nodge87, When you only have one table in a query, all it can do is look at one row at a time. It can look at any column on that row but it can't look at any other row.

So, to compare one table to any other table (which includes itself), you would need two tables in your query.

And the only way to reference two tables in a query is with a join or sub-query.