dongyuan9109 2016-09-09 22:25
浏览 8
已采纳

PHP Hierarchical MYSQL查询

I am trying to make a query on the database example below where if a particular ID has a parent greater than zero, echo that parent’s NAME.

For example when the query would reach ID #5, under the PARENT I would like to get “Adidas” and not “2”. Right now I get the parent’s ID, but I’d like to go a step further and retrieve the parent’s NAME.

DATABASE EXAMPLE:

ID  |  PARENT  |  NAME
———————————————————————
1   |     0    |  Nike
2   |     0    |  Adidas
3   |     1    |  Jordan
4   |     1    |  Bryant
5   |     2    |  Rose
6   |     2    |  Howard

Here is the query I am performing as it stands (it's a bit convoluted since I am calling from 2 tables):

$q = "SELECT `categories`.`ID`, `categories`.`Parent`, `categories`.`NAME`, 
             `products`.`ID` as `ID2`, `products`.`NAME` 
      FROM `categories`, `products`";

Thank you all in advance.

  • 写回答

2条回答 默认 最新

  • dtc66318 2016-09-09 22:57
    关注

    To get the desired output from the query you need to use SELF JOIN in this case as main ID and Parent ID both are in same table.

    The query will be

    SELECT `cat1`.`ID` , `cat1`.`Parent` , `cat2`.`NAME`
    FROM `categories` AS cat1
    LEFT JOIN `categories` AS cat2 ON `cat2`.`ID` = `cat1`.`PARENT` 
    

    Categories table has been joined to itself by creating aliases cat1 and cat2.

    In this case it is important that from which alias you will get name of the Parent. As ID of second alias cat2 is matched with first alias cat1, you will get the parent category name with second alias.

    Hope so it will help you.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 torch框架下的强化学习DQN训练奖励值浮动过低,希望指导如何调整
  • ¥35 西门子博图v16安装密钥提示CryptAcquireContext MS_DEF_PROV Error of containger opening
  • ¥15 mes系统扫码追溯功能
  • ¥40 selenium访问信用中国
  • ¥20 在搭建fabric网络过程中遇到“无法使用新的生命周期”的报错
  • ¥15 Python中关于代码运行报错的问题
  • ¥500 python 的API,有酬谢
  • ¥15 软件冲突问题,软件残留问题
  • ¥30 有没有人会写hLDA,有偿求写,我有一个文档,想通过hLDA得出这个文档的层次主题,有偿有偿!
  • ¥50 有没有人会写hLDA,有偿求写,我有一个文档,想通过hLDA得出这个文档的层次主题,有偿有偿!