Hello all,I'm trying to retrieve a result set from a logical test. I have 3 tables - Orders, Customers and ShipTo.In the table Orders I store a Customer Id as well as a ShipTo Id (for the case when a company has multiple branches and the order is going to be shipped to a different location. When Shipto is the same as the BillTo I insert the value 0 in the ShipTo.ShipToID and use the address from the Customers table instead.I'm currently able to retrieve a single record when I specify an Order ID but I had no luck when trying to retreive the Shipto addresses for multiple orders. Here's the code for the single order (Working):[code}ALTER PROCEDURE [dbo].[OrderSelect]( @Id Int)AS IF (SELECT ShipToId FROM Orders WHERE id=@ID)=0 BEGIN SELECT Orders.Id, Orders.PO, Orders.EntryDate, Customers.Company, Customers.Contact, Customers.Address, Customers.Address2, Customers.Suite, Customers.City, Customers.Province, Customers.PostCode, Customers.Country, Customers.Phone, Customers.Ext, Customers.Fax, Customers.Email, Customers.Contact AS ShiptoContact, Customers.Address AS ShiptoAddress, Customers.Address2 AS ShiptoAddress2, Customers.Suite AS ShiptoSuite, Customers.City AS ShiptoCity, Customers.Province AS ShiptoProvince, Customers.PostCode AS ShiptoPostCode, Customers.Country AS ShiptoCountry, Customers.Phone AS ShiptoPhone, Customers.Ext AS ShiptoExt, Customers.Fax AS ShiptoFax, Customers.Email AS ShiptoEmail FROM Orders INNER JOIN Customers on customers.Id = Orders.CustomerId WHERE (Orders.Id = @Id) END ELSE BEGIN SELECT Orders.Id, Orders.PO, Orders.EntryDate, Customers.Company, Customers.Contact, Customers.Address, Customers.Address2, Customers.Suite, Customers.City, Customers.Province, Customers.PostCode, Customers.Country, Customers.Phone, Customers.Ext, Customers.Fax, Customers.Email, ShipTo.Contact AS ShiptoContact, ShipTo.Address AS ShiptoAddress, ShipTo.Address2 AS ShiptoAddress2, ShipTo.Suite AS ShiptoSuite, ShipTo.City AS ShiptoCity, ShipTo.Province AS ShiptoProvince, ShipTo.PostCode AS ShiptoPostCode, ShipTo.Country AS ShiptoCountry, ShipTo.Phone AS ShiptoPhone, ShipTo.Ext AS ShiptoExt, ShipTo.Fax AS ShiptoFax, ShipTo.Email AS ShiptoEmail FROM ShipTo INNER JOIN Customers ON ShipTo.CustomerID = Customers.Id INNER JOIN Orders ON Customers.Id = Orders.CustomerId WHERE (Orders.Id = @Id); ENDAnd here is my poor attempt for multiple records:ALTER PROCEDURE dbo.GetAddresses(@StartRange int,/*First Order */@EndRange int /*Last*/)AS IF (SELECT ShipToId FROM Orders WHERE orders.id >=1000 AND Orders.Id <=1003)=0 /*just replaced @StartRange and @EndtRange for debuging*/ BEGIN SELECT Customers.Contact AS ShiptoContact, Customers.Address AS ShiptoAddress, Customers.Address2 AS ShiptoAddress2, Customers.Suite AS ShiptoSuite, Customers.City AS ShiptoCity, Customers.Province AS ShiptoProvince, Customers.PostCode AS ShiptoPostCode, Customers.Country AS ShiptoCountry, Customers.Phone AS ShiptoPhone, Customers.Ext AS ShiptoExt, Customers.Fax AS ShiptoFax, Customers.Email AS ShiptoEmail FROM Orders INNER JOIN Customers on customers.Id = Orders.CustomerId; END ELSE BEGIN SELECT ShipTo.Contact AS ShiptoContact, ShipTo.Address AS ShiptoAddress, ShipTo.Address2 AS ShiptoAddress2, ShipTo.Suite AS ShiptoSuite, ShipTo.City AS ShiptoCity, ShipTo.Province AS ShiptoProvince, ShipTo.PostCode AS ShiptoPostCode, ShipTo.Country AS ShiptoCountry, ShipTo.Phone AS ShiptoPhone, ShipTo.Ext AS ShiptoExt, ShipTo.Fax AS ShiptoFax, ShipTo.Email AS ShiptoEmail FROM Orders INNER JOIN ShipTo ON ShipTo.CustomerId = Orders.CustomerId; END[/code]Here is the error:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Any help would be greatly appreciated.Thank you. |
If condition + subquery
Posted - 2008-07-28 : 11:47:14
