Hello,
I'm stucked with a simple search query and would need some help. 😕
I would like a single query to:
-join two tables, A and B (i have keys prepared)
-select rows containing keywords (entered into search box)
-count values for each unique value in one column C1 and
-order rows so that this column C1 has unique values asceding due to their frequency
Brief explanation:
Basically I'm searching through a classified ads database. One table is for categories and the other one for ads. When I join these two, I get rows with category_id, category_name, ad_id, ad_title, ad_description, ad_category_id. Keywords than decide witch to select but I need these resoults returned in a specific maner. They should be sorted by categories but categories must be sorted first on the number of ads they contain in descending order.
Example:
- joined table columns:
category_id category_name ad_id ad_title ad_description ad_category_id
- possible selected values (NOTICE the number of ads, for same category):
3 name3 4 title description 3
4 name4 65 title description 4
4 name4 76 title description 4
2 name2 87 title description 2
2 name2 989 title description 2
2 name2 87 title description 2
1 name1 7 title description 1
- result should be ordered:
2 name2 87 title description 2
2 name2 989 title description 2
2 name2 87 title description 2
4 name4 65 title description 4
4 name4 76 title description 4
3 name3 4 title description 3
1 name1 7 title description 1
My query experiment to do that:
SELECT *, COUNT(category_id) AS count FROM ads, categories WHERE ad_category_id = category_id (search stuff) GROUP BY category_id ORDER BY count
This one is close but I only get the first ad for each category. Categories are ordered ok. I should be getting all ads and repeated unique categories...
Can you provide me with some hints? Thanks. 😕
