从一个表中选择具有父名称的类别 [英] 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屋!