multiple joins, works in sql manager but not in asp.net

jkirkerx

I'm in the process of gathering data for a form I have 3 tables 1. CompletedOrders 2. BillingAddress 3, Shipping Address So it works in SQL Manager when I model it, but not in code, asp.net I just get nothing. In sql manager, the sa doesn't light up pink, and shows like an error, but doesn't complain.

DECLARE @CompletedOrderID INT;
SET @CompletedOrderID = 3;

DECLARE @CA_ID INT, @BA_ID INT, @SA_ID INT, @CIP_ID INT, @CC_ID INT;
SET @CA_ID = (SELECT CA_ID FROM COMPLETEDORDERS WHERE CompletedOrderID = @CompletedOrderID);
SET @BA_ID = (SELECT BA_ID FROM COMPLETEDORDERS WHERE CompletedOrderID = @CompletedOrderID);
SET @SA_ID = (SELECT SA_ID FROM COMPLETEDORDERS WHERE CompletedOrderID = @CompletedOrderID);
SET @CIP_ID = (SELECT CIP_ID FROM COMPLETEDORDERS WHERE CompletedOrderID = @CompletedOrderID);
SET @CC_ID = (SELECT CC_ID FROM COMPLETEDORDERS WHERE CompletedOrderID = @CompletedOrderID);
SELECT
co.CompletedOrderID
, ba.Attention
, ba.CompanyName
, ba.StreetAddress1
, ba.StreetAddress2
, ba.City
, ba.StateCode
, ba.PostalCode
, ba.CountryCode
, ba.Phone
, sa.Attention
, sa.CompanyName
, sa.StreetAddress1
, sa.StreetAddress2
, sa.City
, sa.StateCode
, sa.PostalCode
, sa.CountryCode
, sa.Phone
FROM COMPLETEDORDERS co
LEFT JOIN CUSTOMER_BILLING_ADDRESS ba ON co.BA_ID = @BA_ID
LEFT JOIN CUSTOMER_SHIPPING_ADDRESS sa ON co.SA_ID = @SA_ID
WHERE co.CompletedOrderID = @CompletedOrderID

jkirkerx

How dumb of me, I set the pointer to ByVal instead of ByRef, wiping out the results. Time to go home I think

Richard Deeming

Those ``JOIN``s don't look right. Try: ```sql FROM COMPLETEDORDERS co LEFT JOIN CUSTOMER_BILLING_ADDRESS ba ON co.BA_ID = ba.BA_ID -- I'm guessing the ID column for ba and sa; LEFT JOIN CUSTOMER_SHIPPING_ADDRESS sa ON co.SA_ID = sa.SA_ID -- change them to the correct column names. ``` You've also got five queries to select a single column from the same record. You could replace that with a single ``SELECT``: ```sql SELECT @CA_ID = CA_ID, @BA_ID = BA_ID, @SA_ID = SA_ID, @CIP_ID = CIP_ID, @CC_ID = CC_ID FROM COMPLETEDORDERS WHERE CompletedOrderID = @CompletedOrderID ; ```

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

jkirkerx

I'll try it today or tomorrow, didn't know about the Select and set being able to condense it like that. Thanks!