Weird Cartesian product issue

[Soma_rich]

I have a VERY simple query that looks something like this:

select a.id, b.id
from table1 a, table2 b
where a.id = b.id;

Now table1 has 3 records with the same ID and table2 has three records with the same ID. I expected to get 3 records returned.

I actually get 9. This query has worked fine for about a year. I haev checked the datatype of the ID fields, I haev checked for white spaces but they all look fine.

Any ideas?

 

[Atomic Shrimp]

If you do it with a join instead, does it return anything at all?

 

[Soma_rich]

Hi Shrimp,
You mean like this?

SELECT a.ID, b.ID
FROM table1 a INNER JOIN table2 b ON a.id = b.id;

Same result, even if I do this:

SELECT a.ID, b.ID
FROM table1 a left JOIN table2 b ON a.id = b.id;

 

[Atomic Shrimp]

Oh, wait.. the ID is the same for all three records on both sides?

If so. Yes - what happened (in both cases) is completely normal.

If a field isn't unique, a query joining on it, or using criteria to match to another field in another table will return all possible combinations where a match is found.