dcx693
Registered User.
- Local time
- Yesterday, 21:22
- Joined
- Apr 30, 2003
- Messages
- 3,265
I have two tables with the same structure in a list matching application. I've having a problem understanding the query results, and I've created a simplified example to show the issue.
The first table, tblTestAll has 5 records, the 2nd table, tblTestSome, has 2 records. These records all have the same text value "123123".
So graphically, the tables are like this:
tblTestAll tblTestSome
123123 123123
123123 123123
123123
123123
123123
What I've done is create a query with an equijoin between the two tables on the single field because I wanted to see what records are common to both tables.
Here's the SQL:
SELECT tblTestAll.* FROM tblTestAll INNER JOIN tblTestSome ON tblTestAll.Number = tblTestSome.Number;
The query gives me 10 records back; basically a Cartesian product since all the records have the same value.
Now here's the part I don't understand. When I sort the list, using one of the sort buttons in query view, I only get 5 records. Subsequent re-runs of the query then give me only 5 records unless I close the query without saving and re-open it (in which case it gives me 10 again).
I've figured out that I need to compare the tblTestAll with a version of tblTestSome that has only unique values. I know that 5 records is the correct result since I don't have 10 records anywhere in the database. But I'm confused at the query results.
Anyone have any thoughts? I've attached the simplified copy of the database for your inspection. Run the Query2 query, then sort it, and you'll see what I mean.
The first table, tblTestAll has 5 records, the 2nd table, tblTestSome, has 2 records. These records all have the same text value "123123".
So graphically, the tables are like this:
tblTestAll tblTestSome
123123 123123
123123 123123
123123
123123
123123
What I've done is create a query with an equijoin between the two tables on the single field because I wanted to see what records are common to both tables.
Here's the SQL:
SELECT tblTestAll.* FROM tblTestAll INNER JOIN tblTestSome ON tblTestAll.Number = tblTestSome.Number;
The query gives me 10 records back; basically a Cartesian product since all the records have the same value.
Now here's the part I don't understand. When I sort the list, using one of the sort buttons in query view, I only get 5 records. Subsequent re-runs of the query then give me only 5 records unless I close the query without saving and re-open it (in which case it gives me 10 again).
I've figured out that I need to compare the tblTestAll with a version of tblTestSome that has only unique values. I know that 5 records is the correct result since I don't have 10 records anywhere in the database. But I'm confused at the query results.
Anyone have any thoughts? I've attached the simplified copy of the database for your inspection. Run the Query2 query, then sort it, and you'll see what I mean.