RLLewis
asked on
weird sql problem
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?
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?
ASKER
no, still nothing, unless i pull the symbol line and then it's 8 thousand records
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 and
c.action = 'dvd_cash' and
e.symbol = 'advs' AND
e.countrycode = 'us' AND
e.effectivedate between '4/7/2005' and '4/15/2005' AND
e.uniqueid = 'EQ0017136400001000' --- I added this one
FROM tableA as e INNER JOIN tableB as c
ON e.uniqueid=c.uniqueid and
c.action = 'dvd_cash' and
e.symbol = 'advs' AND
e.countrycode = 'us' AND
e.effectivedate between '4/7/2005' and '4/15/2005' AND
e.uniqueid = 'EQ0017136400001000' --- I added this one
ASKER
still nothing. what am i missing on this?
Check if this will give you anything:
select * from tableB where uniqueid = 'EQ0017136400001000' and action = 'dvd_cash'
If not, then that's the problem.
select * from tableB where uniqueid = 'EQ0017136400001000' and action = 'dvd_cash'
If not, then that's the problem.
ASKER
my bad. i am so sorry rafrancisco -- i was using the wrong action -- this works:
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 and
c.action = 'chg_tkr' and
e.symbol = 'advs' AND
e.countrycode = 'us' AND
e.effectivedate between '4/7/2005' and '4/15/2005'
i do still have on problem, though -- it gives me records for both symbols found where c.uniqueid=e.uniqueid --- i can do the same w/this stmt:
select * from tableB c where exists (select 1 from tableA e where e.symbol = 'advs' and c.action = 'chg_tkr' and c.uniqueid=e.uniqueid and c.uniqueid = 'EQ0017136400001000')
cuz for that id, there's two symbols -- ADVS and ADVSE --- so, do you know how i'd get only the data for the one symbol that i passed it?
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 and
c.action = 'chg_tkr' and
e.symbol = 'advs' AND
e.countrycode = 'us' AND
e.effectivedate between '4/7/2005' and '4/15/2005'
i do still have on problem, though -- it gives me records for both symbols found where c.uniqueid=e.uniqueid --- i can do the same w/this stmt:
select * from tableB c where exists (select 1 from tableA e where e.symbol = 'advs' and c.action = 'chg_tkr' and c.uniqueid=e.uniqueid and c.uniqueid = 'EQ0017136400001000')
cuz for that id, there's two symbols -- ADVS and ADVSE --- so, do you know how i'd get only the data for the one symbol that i passed it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
very true, let me try that
ASKER
perfect. thank you rafran.
ASKER
i'm still giving you points on this, even though the action thing was my bad. thx for your help
You're welcome.
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 and
c.action = 'dvd_cash' and
e.symbol = 'advs' AND
e.countrycode = 'us' AND
e.effectivedate between '4/7/2005' and '4/15/2005'