在一个查询中提取两个多对多

发布于 2024-12-13 08:33:23 字数 360 浏览 0 评论 0原文

我有三个表：国家、新闻和关系表country_news。一个国家有很多新闻，新闻也可以来自多个国家。我想从一个国家/地区进行查找以提取所有相关新闻，这很容易：

SELECT news.* FROM country, news, country_news
WHERE country.id = 1
  AND country_news.country_id = country.id
  AND country_news.news_id = news.id

但现在我还想提取与找到的新闻相关的国家/地区。我没有尝试过多重选择，因为我想尽可能避免它。我尝试了 join 和 group_concat，但无法让它工作。这可以在一个查询中实现吗？

原文

I have three tables: country, news and a relationship table country_news. A country has many news and news can be from many countries. I want to do a look up from a country to extract all the relevant news, which is easy:

SELECT news.* FROM country, news, country_news
WHERE country.id = 1
  AND country_news.country_id = country.id
  AND country_news.news_id = news.id

But now I would like to extract the countries associated with the found news as well. I haven't tried a multiple select because I would like to avoid it when possible. I tried joins and group_concat, but I could not get it to work. Is this possible in one query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

走过海棠暮 2024-12-20 08:33:23

试试这个——

SELECT n.*, c2.* FROM country c
  JOIN country_news cn
    ON cn.country_id = c.id
  JOIN news n
    ON cn.news_id = n.id
  LEFT JOIN country_news cn2
    ON cn2.news_id = n.id
  LEFT JOIN country c2
    ON cn2.country_id = c2.id
WHERE c.id = 1

Try this one -

SELECT n.*, c2.* FROM country c
  JOIN country_news cn
    ON cn.country_id = c.id
  JOIN news n
    ON cn.news_id = n.id
  LEFT JOIN country_news cn2
    ON cn2.news_id = n.id
  LEFT JOIN country c2
    ON cn2.country_id = c2.id
WHERE c.id = 1

回复收藏 0 原文

倾城花音 2024-12-20 08:33:23

Select country.id from country c right Join  
country_news cn on c.id=cn.country_id 
where  cn.news_id = @newsid

应该为您提供特定 newsid 的所有国家/地区 ID

Select country.id from country c right Join  
country_news cn on c.id=cn.country_id 
where  cn.news_id = @newsid

should give you all country ids for a specific newsid

回复收藏 0 原文

删除→记忆 2024-12-20 08:33:23

您还应该选择特定于国家/地区的列。

SELECT country.name as country_name, news.* FROM country, news, country_news WHERE country.id = 1 AND country_news.country_id = country.id AND country_news.news_id = news.id;

You should just select country specific columns as well.

SELECT country.name as country_name, news.* FROM country, news, country_news WHERE country.id = 1 AND country_news.country_id = country.id AND country_news.news_id = news.id;

回复收藏 0 原文

~没有更多了~