First of, sorry for the title, I'm not sure how to name the question.
Basically, I'm writing a basic search engine based on a location and categories.
I have a table events, a table events_categories and a table categories. Object have a column city and a column state
One event have and belongs to many categories, and one category have and belongs to many events.
I currently have the following query:
select * from events LEFT OUTER JOIN "events_categories" ON "events_categories"."event_id" = "events"."id" LEFT OUTER JOIN "categories" ON "categories"."id" = "events_categories"."category_id" WHERE "categories"."id" = 2 OR "categories"."id" = 3 order by city!='Paris', state!='TX'
It works but I'd like to order my result based on the number of category matched and I also have duplicated row. I'd like to remove them
Here is the sqlfiddle I use
The expected result should be:
- meetup #2
- meetup #1
- meetup #4
- meetup #16
- meetup #5
- meetup #7
- meetup #8