I have the following set up in a database
Customer makes Orders (1 to M)
Orders contain Products (M to M)
Products have a configuration (M to 1 - e.g. many different products belong to the same configuration)
Configurations belong to a standard (M to 1 - many different configurations belong to the same standard)
Standards belong to a Category (M to 1 - many different standards belong to the same category)
Given a particular order ID i have to get all of the products in that order and then sort the products so they are arranged into groups of categories. Within a category they have to be sorted into standards. Within a standard they have to be sorted into groups. For example:
I don't know how to sort by query so that the products are returned in this order.
The query also has to contain details of the category name, the standard name and the configuration name.
Here is my query so far:
select * from orders 0
inner join order details OD on 0.OrderID = Od.oRDERid
iiner join products P on P.ProductID = OD.OrderID
inner join configuration CF on P.ConfigID = CF.ConfigID
inner join standard S on s.standardID = CF.standardID
inner join category CAT on s.categoruyID = cat.categoryID
where O.oRDERid = @ORDERid
Like I say, it's the sorting of the products into the appropriate order that I don't know how to do.
Thanks in advance