Click here to Skip to main content
15,867,785 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
My query is -:
SQL
select salesDetails.bill_no,custDetails.custName,custDetails.custAdd from salesDetails,custDetails where custDetails.custID=(select salesDetails.custID from salesDetails where slsDate between #" + DTP1.Value.Date.ToShortDateString() + "# and #" +DTP2.Value.Date.ToShortDateString() + "#)


when i run this query It's give an error "At most one record can be returned by this subquery".
I want to get all the bill_no between given date and get that customer details from custDetails table specific custID...

thanks in advance....
Posted
Updated 30-Jan-21 9:44am
v2
Comments
Richard C Bishop 25-Feb-13 13:08pm    
You are not joining your tables you are selecting from.
JayantaChatterjee 25-Feb-13 13:14pm    
Ooops!! I missed That....
Thanks....

You could change from WHERE custID = to custID IN
SQL
select salesDetails.bill_no,custDetails.custName,custDetails.custAdd from salesDetails,custDetails where custDetails.custID IN (select salesDetails.custID from salesDetails where slsDate between #" + DTP1.Value.Date.ToShortDateString() + "# and #" +DTP2.Value.Date.ToShortDateString() + "#)
 
Share this answer
 
Comments
JayantaChatterjee 25-Feb-13 13:25pm    
Sir it returns the same row two times...
ZurdoDev 25-Feb-13 13:43pm    
Oh, sorry, I didn't look closely at your SQL. I was only looking at your question. You are doing a cross join by having "FROM salesDetails, custDetails." What you really need to do, now that I looked closer, is to do a join and not a subquery. See if this works.

SELECT s.bill_no, c.custName, c.custAdd
FROM salesDetails s
INNER JOIN custDetails c ON s.custID = c.custID
WHERE s.slsDate BETWEEN date1 AND date2
JayantaChatterjee 25-Feb-13 13:48pm    
Thankssss a Lottttt Sir... :-)
ZurdoDev 25-Feb-13 13:51pm    
You're welcome. Sorry I missed that the first time.
JayantaChatterjee 25-Feb-13 13:56pm    
It's Okay..
Sir, You solve that Problem....
Thanks Again... :-)
SELECT (SELECT TOP 1 Savamt FROM Trans AS Dup WHERE Dup.Trans_ID
 
Share this answer
 
SQL error "At most one record can be returned by this subquery."
My query is -:SELECT (SELECT TOP 1 Savamt FROM Trans AS Dup WHERE Dup.Trans_ID
 
Share this answer
 
Comments
Richard Deeming 1-Feb-21 9:41am    
Your question is not a "solution" to someone else's question. And it's certainly not two solutions!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900