SQL Select 语句加入 [英] SQL Select Statement joining

问题描述

我有一张 oc_category_description 表，其中的列是:

I have one table of oc_category_description where columns are :

• category_id
• 姓名

和其他表 oc_category 其中列:

and other table oc_category where columns are :

• category_id
• 图片
• parent_id

这里是 oc_category_description 表的示例图片

Here the sample pic of oc_category_description table

oc_category 表

oc_category table

这里我想显示名称、category_id、图像、parent_id，其中 oc_category parent_id 为 0；

Here i am want to show name, category_id, image, parent_id where oc_category parent_id is 0;

这里是sql:

php

function getMainCategory()
{
    $stmt = $this->con->prepare("SELECT category_id, image, parent_id, (SELECT oc_category_description.name FROM oc_category_description WHERE oc_category.category_id = oc_category_description.category_id) FROM oc_category WHERE parent_id = 0 ORDER BY category_id ASC");

    $stmt->execute();
    $stmt->bind_result($category_id, $image, $parent_id, $name);

    $users = array();

    while ($stmt->fetch()) {
        $temp = array();
        $temp['category_id'] = $category_id;
        $temp['image'] = $image;
        $temp['parent_id'] = $parent_id;
        $temp['name'] = $name;

        array_push($users, $temp);
    }
    return $users;
}

但它什么都不返回:(

推荐答案

Use Join

SELECT ocd.category_id, image, parent_id, name
FROM oc_category_description  AS ocd
INNER JOIN oc_category 
WHERE parent_id = 0 
ORDER BY ocd.category_id ASC

这篇关于SQL Select 语句加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！