[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

weird sql problem

Posted on 2005-04-29
11
Medium Priority
?
279 Views
Last Modified: 2010-03-19
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
Comment
Question by:RLLewis
  • 6
  • 5
11 Comments
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13897417
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
 
LVL 1

Author Comment

by:RLLewis
ID: 13897480
no, still nothing, unless i pull the symbol line and then it's 8 thousand records
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13897493
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 1

Author Comment

by:RLLewis
ID: 13897549
still nothing.  what am i missing on this?
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13897599
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
 
LVL 1

Author Comment

by:RLLewis
ID: 13897602
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
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13897665
>> 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
 
LVL 1

Author Comment

by:RLLewis
ID: 13897670
very true, let me try that
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13897688
perfect.  thank you rafran.  
0
 
LVL 1

Author Comment

by:RLLewis
ID: 13897693
i'm still giving you points on this, even though the action thing was my bad.  thx for your help
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13897695
You're welcome.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question