Another mysql JOIN question...

Question

I have these tables:

classified:
classified_id (PK)
price
headline
cat_id // THIS IS ANYTHING FROM 1 TO 30 DEPENDING ON CATEGORY. IT IS SO THAT I CAN LINK WHICH CATEGORY TO USE IN THE CATEGORY TABLE BELOW
text
etc...

category:
cat_id (PK)
cat_name

category_options:
option_id (PK)
cat_id (FK) // FOREIGN KEY FROM CATEGORY TABLE...
option_name

option_values:
value_id (PK)
option_id (FK)
classified_id (FK)
value

How should I use join here, could anybody give me a quick example?

Here is an example of my setup:

      category
cat_id       cat_name
  1            cars

         category_options
option_id     cat_id    option_name 
   1             1         color
   2             1        gearbox

         option_values
 value_id       option_id       classified_id      value
    1              1                 22             red
    2              2                 22            manual

         classified
classified_id      price        headline         cat_id
    22              5000        'test'              1 //for cars

I want to be able to retrieve all options and their values from one category (in this ex cars) by only 'knowing' classified_id (which is 22 in this case).

Basically, I need help with the join statement...

and please don't use aliases in the code to simplify it for me :)

Thanks

Answer 1

Something like

SELECT category_option.option_name, option_values.value FROM classified, category_option, option_values WHERE classified.classified_id=?id AND classified.cat_id=category_options.cat_id AND option_values.option_id=category_options.option_id

If you passed in 22 for the ?id parameter, you'd get 2 rows:

Color Red

Gearbox Manual

Answer 2

I know this doesn't answer your main question, but I'd like to offer a suggestion that I believe will make your life easier...

1. Column names for PK columns -- just call all of them "id". Reduces the number of things you have to remember. Eliminates a major source of confusion and potential bugs.

2. Make table names consistent. I mean make them all the same form. You could make them all a singular noun or all a plural noun, but just make them all the same. Queries become easier to write and easier to understand.

   classifieds

   categories

   category_options

   option_values

3. Column names for FK cols -- like this: parent_table_id. For example: classified.category_id.

4. Eliminate any verbiage that doesn't contain new information. For example, category.name instead of category.cat_name.

Answer 3

You actually don't even need to explicitely specify the join here. It's just as simple that you want to get values from two tables (options and their values) where the option_ids are identical, and select only thos results where your cat & classified id matches.

SELECT cat_id, classified_id, option_id, option_name, value
  FROM option_values, category_options
 WHERE category_options.option_id = option_values.option_id
   AND classified_id = <?> AND cat_id = <?>