mysql - MySQL sort query by matches from other tables

Question

I have the following tables:

• news (id, title, ... )
• news_exchange_relations (id, news_id, exchange_id)
• news_currency_relations (id, news_id, currency_id)
• news_country_relations (id, news_id, country_id)

I have a search form with three parameters: exchange, currency and country. My goal is by selecting some of the parameters to find those news which are most relevant to the selected parameters.

Example: If the user has specified X parameters I want to show all news which match at least one of the parameters, but first I will display news matching X parameters, than X-1 and so on.

Detailed example:

News:

1, title 1
2, title 2
3, title 3

News exchange relations:

1, 1, 1
2, 2, 1

News country relations:

1, 1, 1
2, 2, 1

News currency relations:

1, 1, 1
2, 2, 2

Search parameters:

Country =1, exchange = 1, currency = 1

Results:

Id | title | matches
1, title1, 3
2, title2, 2

Is it possible that to achieve that only with MySQL ?

标签： mysqlsql

Answer 1

You can start with this:

create table want as
select a.*,
(case when b.id is null then 0 else 1 end) + (case when c.id is null then 0 else 1 end) + (case when d.id is null then 0 else 1 end) as matches
from
news a
left join
(select * from news_exchange_relations
 where exchange_id = 1) b
on a.id = b.id
left join
(select * from news_currency_relations
 where currency_id = 1) c
on a.id = c.id
left join
(select * from news_country_relations
 where country_id = 1) d
on a.id = d.id
having matches > 0;  

Do let me know in case of any clarifications.