Another mysql JOIN 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
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...
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.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
Column names for FK cols -- like this:
parent_table_id
. For example:classified.category_id
.Eliminate any verbiage that doesn't contain new information. For example,
category.name
instead ofcategory.cat_name
.
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 = <?>
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
精彩评论