Problem with join returning zero rows
Hello, I am trying to do a select with joins on a IQ16 server, the query is returning 0 rows when it should be returning 23 rows. If I use a subquery instead of a join, it correctly returns the 23 expected rows.
select count(*) from t1, t2 where t1.ID = t2.ID and t2.misc = 'A'
returns 0 rows
select count(*) from t1 where t1.ID in (select t2.ID from t2 where t2.misc = 'A')
returns 23 rows
I can't explain this behaviour. I've checked the datatypes and and data match (thankfully these are very small tables), there are no hidden characters, i.e. I compared select len(ID) from t1 and select len(ID) from t2 and they match. ID is a varchar(30) in both tables. I don't understand.
Any help with this mystery would be appreciated!