• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

help needed with sql query

Hi

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:

Category 1
  Standard 1
     Configuration 1
       Product a
       Product b
    Configuration 2
       Product c
       Product d

  Standard 2
     Configuration 3
       Product e
       Product f
    Configuration 4
       Product g
       Product h

Category 2
  Standard 3
     Configuration5
       Product i
       Product j
    Configuration 6
       Product k
       Product l

  Standard 4
     Configuration 7
       Product m
       Product n
    Configuration 8
       Product o
       Product p


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
andrea
0
andieje
Asked:
andieje
2 Solutions
 
wstuphCommented:
You mean like this?

order by CategoryID, StandardID, ConfigurationID, ProductID

Assuming you actually have those fields - there's no way to tell w/ what you've posted.
0
 
disentropyCommented:
Try this:

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
ORDER BY cat.categoryID,CF.standardID,CF.ConfigID,P.ProductID
0
 
andiejeAuthor Commented:
i'm sorry i confused myself. it was the middle of the night when i posted. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now