loop through records to retrieve more info

[CSharpBeginner]

I am new to SQL and I'm trying to write a storedprocedure that will search a customer table, get various customer info, then for each order associated with that customer, get the OrderType of each order.

Is there anyway to loop through the records in SQL since I don't have a pre-defined amount of records.

The key data model information looks like this:

Customer:
CustomerID (PK)

Order:
OrderID (PK)
CustomerID (FK)

OrderDetails:
OrderID (FK)
OrderType (FK)

OrderType:
OrderTypeID (PK)
OrderTypeName, etc.

I want to get the OrderTypeID for each order associated with this customer and I'm not certain how to do that.

Thanks for any help!!

 

[SQLSister]

First thing you need to do is stop even thinking of the idea of looping through records. In SQL Server you are best servered thinking, "How can I affect a set of records?"

What you need is to join the tables. I suggest that first you read about the various types of joins in Books Online.

Now from your structure, it looks to me as if there will be multiple types of order per customer and per order.

Something like this can get you started, but read about joins so that you understand what it is doing.

Select c.customerID, o.OrderID, d.OrderType from
Customer c join [Order] o
on c.CustomerID = o.Customerid
join OrderDetails d
On o.orderID = d.OrderType

Additionally, you will note that the Order table is refernced in brackets. That is becasue order is a SQL key word. FOr SQL Server to regognize this as a refernce to the table you need to put it in brackets. It is a bad idea to use key words as table names or field names. IF you database is not too far along, I would change the name of this table.







Questions about posting. See faq183-874

 

[CSharpBeginner]

Thanks - I figured out why I was having so much trouble - I was trying to use the Customer table for the from statement and due to columns and FKs, I should have used the Order table.
This is what I ended up with:

select o.CustomerID, c.lastName + ', ' c.firstName as Customer, rderID, ot.OrderTypeName

from [order] o
left join Customer c
on o.CustomerID = c.CustomerID
left join OrderDetails od
on rderID = od.OrderID
left join OrderType ot
on ot.OrderTypeID = od.OrderTypeID
where CustomerID = @CustomerID

And it works. Thanks for pointing me in the right direction.