At most one record can be returned by this subquery

[avsesis]

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

[deepuv04]

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...

SELECT O.WorksOrderNumber, O.ExtraOrderNo, left(O.OrderDateCreated, 10) as OrderDateCreated, 
    O.DeliveryWeek, O.OrderID, O.OEMOrder, O.BoatGenID,L1.SailType AS Lineitem1, L2.SailType AS Lineitem2
FROM Order_Tbl O INNER JOIN
     (   SELECT distinct O.OrderID,(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 L1 
             on  L1.OrderID = O.OrderID
    (   SELECT distinct O.OrderID,(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 L2
              ON L2.OrderID = O.OrderID
WHERE O.OEMOrder=True and O.BoatGenID=" & DDLBoatType.SelectedValue & " 
order by OrderID desc