在单个查询中查询2个表 [英] Querying 2 Tables in a single query

问题描述

我有一个帖子表和一个类别表，我想要的是选择特定类别的帖子.问题在于类别存储在另一个表中而不是在posts表中，这里是示例:

I have a table for posts and a table for categories, what i want is to select posts that are in a specific category. The problem is that the category is stored in another table and not in the posts table, here is the example:

id   title       body
---------------------------
125  Some title  Blah blah

类别

postid  category
----------------
125     politic

我希望在单个查询中以示例方式获取政治类别中的帖子，该怎么办?

I want in single query to fetch posts in the politic category by example, what to do?

推荐答案

使用:

SELECT p.id,
       p.title, 
       p.body
  FROM POSTS p
  JOIN CATEGORIES c ON c.postid = p.id
 WHERE c.category = 'politic'

我的CATEGORIES表存在一个问题，就是将类别值存储为字符串表示数据未标准化-您应该使用CATEGORY表:

The issue I have with your CATEGORIES table is that storing the category value as a string means the data isn't normalized - you should instead have a CATEGORY table:

• category_id(主键，自动递增)
• category_description

...并使用CATEGORIES表中的category_id值:

...and use the category_id value in the CATEGORIES table:

• category_id(主键，CATEGORY.category_id的外键)
• post_id(主键，POSTS.postid的外键)

这篇关于在单个查询中查询2个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！