By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,397 Members | 1,393 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,397 IT Pros & Developers. It's quick & easy.

At most one record can be returned by this subquery

P: 1
Hi - I have spent all morning surfing this problem and cannot find a match that might help so am asking for help.
I want to fill a dataset with information about orders placed for a particular type of boat. This information comes from my Order_Tbl. In two of the columns I want to have information about lineitems for the particular order. This information is coming from the OrderLineitem_Tbl but to access the name of the sail I need to look in the QuoteSailType_Tbl as only the key is stored in the OrderLineitem_Tbl.
I have tried each individual subquery which works if I can give the actual OrderID, however when I try and place it all together using the OrderID from the first part of the query, I get the multiple sail types associated with the GenBoatID not the OrderID.
Can anyone help please.
Here is my SQL statement
SELECT O.WorksOrderNumber, O.ExtraOrderNo, left(O.OrderDateCreated, 10) as OrderDateCreated, O.DeliveryWeek, O.OrderID, O.OEMOrder, O.BoatGenID, (SELECT distinct (QuoteSailType_Tbl.SailType) as SailType FROM (Order_Tbl AS O INNER JOIN OrderLineItem_Tbl ON O.OrderID = OrderLineItem_Tbl.OrderID) INNER JOIN QuoteSailType_Tbl ON OrderLineItem_Tbl.QuoteSailTypeID = QuoteSailType_Tbl.QuoteSailTypeID WHERE (((OrderLineItem_Tbl.LineItem)=1) and ((O.OrderID)=OrderLineItem_Tbl.OrderID))) as Lineitem1, (SELECT distinct (QuoteSailType_Tbl.SailType) as SailType FROM (Order_Tbl AS O INNER JOIN OrderLineItem_Tbl ON O.OrderID = OrderLineItem_Tbl.OrderID) INNER JOIN QuoteSailType_Tbl ON OrderLineItem_Tbl.QuoteSailTypeID = QuoteSailType_Tbl.QuoteSailTypeID WHERE (((OrderLineItem_Tbl.LineItem)=2) and ((O.OrderID)=OrderLineItem_Tbl.OrderID))) as Lineitem2 FROM Order_Tbl O WHERE O.OEMOrder=True and O.BoatGenID=" & DDLBoatType.SelectedValue & " order by OrderID desc
Mar 10 '08 #1
Share this Question
Share on Google+
1 Reply


deepuv04
Expert 100+
P: 227
Hi - I have spent all morning surfing this problem and cannot find a match that might help so am asking for help.
I want to fill a dataset with information about orders placed for a particular type of boat. This information comes from my Order_Tbl. In two of the columns I want to have information about lineitems for the particular order. This information is coming from the OrderLineitem_Tbl but to access the name of the sail I need to look in the QuoteSailType_Tbl as only the key is stored in the OrderLineitem_Tbl.
I have tried each individual subquery which works if I can give the actual OrderID, however when I try and place it all together using the OrderID from the first part of the query, I get the multiple sail types associated with the GenBoatID not the OrderID.
Can anyone help please.
Here is my SQL statement
SELECT O.WorksOrderNumber, O.ExtraOrderNo, left(O.OrderDateCreated, 10) as OrderDateCreated, O.DeliveryWeek, O.OrderID, O.OEMOrder, O.BoatGenID, (SELECT distinct (QuoteSailType_Tbl.SailType) as SailType FROM (Order_Tbl AS O INNER JOIN OrderLineItem_Tbl ON O.OrderID = OrderLineItem_Tbl.OrderID) INNER JOIN QuoteSailType_Tbl ON OrderLineItem_Tbl.QuoteSailTypeID = QuoteSailType_Tbl.QuoteSailTypeID WHERE (((OrderLineItem_Tbl.LineItem)=1) and ((O.OrderID)=OrderLineItem_Tbl.OrderID))) as Lineitem1, (SELECT distinct (QuoteSailType_Tbl.SailType) as SailType FROM (Order_Tbl AS O INNER JOIN OrderLineItem_Tbl ON O.OrderID = OrderLineItem_Tbl.OrderID) INNER JOIN QuoteSailType_Tbl ON OrderLineItem_Tbl.QuoteSailTypeID = QuoteSailType_Tbl.QuoteSailTypeID WHERE (((OrderLineItem_Tbl.LineItem)=2) and ((O.OrderID)=OrderLineItem_Tbl.OrderID))) as Lineitem2 FROM Order_Tbl O WHERE O.OEMOrder=True and O.BoatGenID=" & DDLBoatType.SelectedValue & " order by OrderID desc
Try the following query....
if you want to return at least one record use left outer join...

Expand|Select|Wrap|Line Numbers
  1. SELECT O.WorksOrderNumber, O.ExtraOrderNo, left(O.OrderDateCreated, 10) as OrderDateCreated, 
  2.     O.DeliveryWeek, O.OrderID, O.OEMOrder, O.BoatGenID,L1.SailType AS Lineitem1, L2.SailType AS Lineitem2
  3. FROM Order_Tbl O INNER JOIN
  4.      (   SELECT distinct O.OrderID,(QuoteSailType_Tbl.SailType) as SailType 
  5.         FROM (Order_Tbl AS O INNER JOIN 
  6.         OrderLineItem_Tbl ON O.OrderID = OrderLineItem_Tbl.OrderID) INNER JOIN 
  7.         QuoteSailType_Tbl ON OrderLineItem_Tbl.QuoteSailTypeID = QuoteSailType_Tbl.QuoteSailTypeID 
  8.         WHERE (((OrderLineItem_Tbl.LineItem)=1) and ((O.OrderID)=OrderLineItem_Tbl.OrderID))) as L1 
  9.              on  L1.OrderID = O.OrderID
  10.     (   SELECT distinct O.OrderID,(QuoteSailType_Tbl.SailType) as SailType 
  11.         FROM (Order_Tbl AS O INNER JOIN 
  12.         OrderLineItem_Tbl ON O.OrderID = OrderLineItem_Tbl.OrderID) INNER JOIN 
  13.         QuoteSailType_Tbl ON OrderLineItem_Tbl.QuoteSailTypeID = QuoteSailType_Tbl.QuoteSailTypeID 
  14.         WHERE (((OrderLineItem_Tbl.LineItem)=2) and ((O.OrderID)=OrderLineItem_Tbl.OrderID))) as L2
  15.               ON L2.OrderID = O.OrderID
  16. WHERE O.OEMOrder=True and O.BoatGenID=" & DDLBoatType.SelectedValue & " 
  17. order by OrderID desc
  18.  
  19.  
Mar 10 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.