sql select returning multiple results per field

Question

I would appreciate assistance with the following:

I have two tables Data and Results. Data has the columns date, stock_price, strike_price Results has the column earnings_date

Data has about 1m rows and for each date there are many strike_price entries but one stock_price. e.g.

date stock_price strike_price
- 4/1/2013 100 75
- 4/1/2013 100 85
- 4/1/2013 100 95
- 4/2/2013 102 75
- 4/2/2013 102 85
- 4/2/2013 102 95
- 4/3/2013 105 75
- 4/3/2013 105 85
- 4/3/2013 105 85

What I am trying to do is, when in the Results table, find the stock_price for each earnings date.

So for example Results:

earnings_date stock_price
4/1/2013 100
4/2/2013 102
4/3/2013 105

I have tried the following:

select distinct Data.stock_price from Data join Results on Data.date = Results.earnings_date

but I get all the stock prices in each row:

earnings_date stock_price
4/1/2013 100102105
4/2/2013 100102105
4/2/2013 100102105

Any assistance appreciated. (using Filemaker pro 12)

Do Data and Results tables have same number of rows? I mean for all date in Data table, are there corresponding earning_date in Results table? — Jivan
select Results.earnings_date, Data.stock_price from Data join Results on Data.date = Results.earnings_date group by Results.earnings_date,Data.stock_price — Dinup Kandel
Zane: no data has close to 1m rows, results has a couple of hundred. thanks — Sam_H
@DinupKandel: thanks but I still get multiple results in one field although it picked up a few results my original statement had missed. Any suggestions? — Sam_H

Jivan Jivan · Accepted Answer · 2013-07-02T12:55:13

You can try this:

select r.earnings_date, d.stock_price from Data d, Results r
where d.date = r.earnings_date 
group by r.earnings_date,d.stock_price;

I think, it should work, for example, I'd checked it with Data table with 5 rows & Results table with 3 rows. If problem persists, add comment below.

sql select returning multiple results per field

2 Answers