weird sql problem
Posted on 2005-04-29
joins or correlated subqueries --- i've got this query which returns nothing:
SELECT e.effectivedate, c.col025 as Amount, c.col035 as Type
FROM tableA as e INNER JOIN tableB as c ON e.uniqueid=c.uniqueid
where c.action = 'dvd_cash'
and e.symbol = 'advs'
AND e.countrycode = 'us'
AND e.effectivedate between '4/7/2005' and '4/15/2005'
the query works if i comment out the symbol line BUT it brings back well over 8K records. i expect 2 records. see, this query works, and gives me 4 records:
select effectivedate, symbol,countrycode from tableA where symbol = 'advs' and countrycode = 'us' and uniqueid = 'EQ0017136400001000'
and this query works and gives me 2 records:
select * from tableB where uniqueid = 'EQ0017136400001000'
the date parameter actually kicks out 2 of the records in tableA, so the expected result is 2 records, not 8,252
what's the best way to be doing this?