complex sql query help.

[mes123]

I have a db with the following tables:
Products
Products_to_categories
Categories
Accessories

I am trying to make a query which will list all the products in a specific cateory and for each product list the model number of each accessory and the importance_level.

The table setups are as follows:

Productc conatains the PK products_id and the field products_model.

Products_to_categories:
+---------------+---------+
| Field | Type |
+---------------+---------+
| products_id | int(11) |
| categories_id | int(11) |
+---------------+---------+

categories

+------------------+-------------+
| Field | Type |
+------------------+-------------+
| categories_id | int(11) |
| categories_image | varchar(64) |
| parent_id | int(11) |
| sort_order | int(3) |
| date_added | datetime |
| last_modified | datetime |
+------------------+-------------+


+------------------+---------+------+-----+---------+
| Field | Type | Null | Key | Default |
+------------------+---------+------+-----+---------+
| main_product_id | int(11) | | | 0 |
| sub_product_id | int(11) | | | 0 |
| importance_level | int(11) | | | 0 |
+------------------+---------+------+-----+---------+

so ideally for the result I would have a table with the follwoing columns

products.products_model, accessories.products_model, accessories.importance_level

with results grouped by products.products_model then by accessories.products_model

I'm still learning SQL so this one is a bit out of my depth at the moment.

any help would be appreciated.

 

[r937]

select products.products_model
     , accessories.products_model
     , accessories.importance_level
  from ...
order
    by products.products_model 
     , accessories.products_model

r937.com | rudy.ca