从一个表中选择具有父名称的类别 [英] select categories with parent name from one table

问题描述

我有一个现有的产品数据库，我正在用它编写一个管理工具(用PHP). 该数据库包含以下类别"表:

I got an existing products database which I'm writing an administration tool for (in PHP). The database contains the following "categories" table:

Table Categories
--------------------
PK | id
FK | parent_id
   | title

现在，外键"parent_id"包含来自同一表的ID，如果是最高类别，则为"0".

Now the foreign key "parent_id" contains an id taken from the same table, or "0" if it's a topmost category.

为了创建概述，我现在需要一个mysql语句，该语句将产生以下数据:

For creating an overview I now need a mysql statement which results in the following data:

id | parent_id | title | parent_title

parent_title是我不知道的地方.我创建了以下语句:

The parent_title is where I've no idea. I created the following statement:

SELECT 
  c1.id, 
  c1.parent_id, 
  c1.title, 
  c2.title as `parent_title`
FROM 
  categories c1, 
  categories c2 
WHERE 
  c1.parent_id = c2.id

我现在只获得所有具有父类别的类别.

I now only get all categories which have got a parent category.

应该很简单，并且可能已经在这里得到了回答.我想我只是没有找到合适的词来搜索现有文章.

Should be simple, and might have already been answered here. I think I only didn't find the right words to search for to find it by searching existing articles.

感谢您的帮助， 丹尼尔

Thanks for your help, Daniel

推荐答案

您可以使用

You can use a LEFT OUTER JOIN for this:

SELECT c1.id,  
  c1.parent_id,  
  c1.title,  
  c2.title as `parent_title` 
FROM categories c1  
left outer join categories c2 on c1.parent_id = c2.id 

这篇关于从一个表中选择具有父名称的类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！