Problem with subquery returning more than 1 value [Solved]

[dan sh]

You might be having something like this:

select columns from tablename where somecolumn = (select someColumn1 from sometableone)

Now, that subquery is returning multiple values and hence "=" won't work. Replace "=" by "in" or check the sub query if it should return only one value.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

[Ashfield]

Show us what you have and someone will help.

Bob Ashfield Consultants Ltd Proud to be a Code Project MVP

[James Shao]

Last night I had some trouble pasting the codes properly in the forum, fortunately the problem is fixed now. Yes I currently have: SELECT Ticker FROM Table WHERE Date = '11/20/2009' AND Price > (Select Price from Table WHERE Date = '11/17/2009')

[James Shao]

Thanks Danish, I've posted in a reply thread below.   The operator I am using is a ">" instead of a "=", so using "IN" probably won't work for me.   Could you please let me know how I can get around this problem?   I'm not really sure why subquery would still return more than 1 result, since I've specified that Table.Ticker = t2.Ticker and Date = a specific date in the subquery, thus it should only return the price on that specific date for that specific stock.... Thanks in advance!

[dxlee]

You can use a join similar to this one:

select a.ticker from table as a inner join table as b
on a.ticker=b.ticker and a.price>b.price
where a.date='11/20/2009' and b.date='11/17/2009'
group by a.ticker;

[Corporal Agarn]

You can always you TOP 1.

SELECT TOP 1 mycolumn FROM mytable

This probably will not work for your case but I thought I would throw it in.

[dan sh]

You must be having multiple prices for that date. If you need the top record only, use order by and top 1 in you statement.

50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

[James Shao]

Thank you this also works   djj55   :)

[James Shao]

Thank you D@nish for the tip, yes indeed there are multiple prices on same dates belonging to a ticker, though that shouldn't happen.   Bad data, may need to redownload. Top 1 solves this for now.   Many thanks again.

[James Shao]

Thank you Dxlee, not only that it works, I learned a new way of using inner join table!   I used to think inner join is only for joining two different tables, but now I see that you can use it to link to the same table filtered for some criterias.   This is going to change the way I write queries from now on   :-D Could you please recommend a good online source where I can learn how to use SQL more effectively?   Using INNER JOIN in this manner (seems creative and ingenious to me) was not covered in W3Schools nor in most SQL tutorial websites.   Many thanks again!

[dxlee]

I think d@nish's solution is better for your case, even though you found my inner join to be new and helpful to you. I am sorry I don't have a book or website to recommend to you on SQL. I learned something about SQL by reading an Oracle manual on SQL*Plus (which has lots of very interesting examples) about 20 years ago (in 1990) when I was working on a project for a friend. I moved a few times (to different countries) ever since and lost that manual. It was for an old version of Oracle anyway (which ran on VMS with no graphical user interface.) The new Oracle manuals seem to have nothing similar to the one I read. Your best bet is to find a good book on SQL.