• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

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?
0
RLLewis
Asked:
RLLewis
  • 6
  • 5
1 Solution
 
rafranciscoCommented:
Try 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 and
          c.action = 'dvd_cash' and
          e.symbol = 'advs' AND
          e.countrycode = 'us' AND
          e.effectivedate between '4/7/2005' and '4/15/2005'
0
 
RLLewisAuthor Commented:
no, still nothing, unless i pull the symbol line and then it's 8 thousand records
0
 
rafranciscoCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
RLLewisAuthor Commented:
still nothing.  what am i missing on this?
0
 
rafranciscoCommented:
Check if this will give you anything:

select * from tableB where uniqueid = 'EQ0017136400001000' and action = 'dvd_cash'

If not, then that's the problem.
0
 
RLLewisAuthor Commented:
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?

0
 
rafranciscoCommented:
>> it gives me records for both symbols found where c.uniqueid=e.uniqueid <<

It is not returning the records for both symbols.  The reason why you're getting 2 records is because there are 2 records in TableB that match your criteria.

Will adding a DISTINCT work?

SELECT DISTINCT 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'
0
 
RLLewisAuthor Commented:
very true, let me try that
0
 
RLLewisAuthor Commented:
perfect.  thank you rafran.  
0
 
RLLewisAuthor Commented:
i'm still giving you points on this, even though the action thing was my bad.  thx for your help
0
 
rafranciscoCommented:
You're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now