从两个Mysql表获取信息 [英] Getting info from two Mysql Tables

问题描述

我正在使用Codeigniter，并且尝试从两个表中调用信息：

I am using Codeigniter and I am trying to call info from two tables:

产品：
id，
名称，
价格，
描述，
typeId

Product: id, name, price, description, typeId

产品类型：
tCategory，
tName

Product_Type: tCategory, tName

我正在尝试从Product中提取所有信息，并使用Product.typeID来匹配Product_Type表，而仅返回tName。大多数情况下，Product_Type表中至少会有3行具有相同的typeID。示例：

I am trying to pull all info from Product and use Product.typeID to match to the Product_Type table and only pull back the tName. Most of the time there will be at least 3 rows from Product_Type table that will have the same typeID. Example:

产品1将是一件20美元的红色衬衫，而从类型I中将需要大，中和小。

Product 1 will be a red shirt for $20 and from type I will need Large, Medium and Small.

我尝试使用JOIN进行此操作，但这为我提供了我需要的3种类型，但也将衬衫信息重复了3次。

I have tried to doing this with JOIN but it gives me the 3 types I need but also duplicate the shirt info 3 times.

这是我的代码：

$this->db->select('product.id, product.name, product.price, product.description, product_type.tName');  
$this->db->from('product');
$this->db->where('perm_name', $id);
$this->db->join('product_type', 'product_type.tCategory = product.typeId', 'LEFT OUTER');
$query = $this->db->get(); 

任何帮助将不胜感激。

Any help would be greatly appreciated.

编辑：

Array
(
    stdClass Object
        (
            [id] => 2
            [name] => Tshirt 1
            [price] => 20
            [description] => Awesome tshirt
            [tName] => 1
    )

)

Array
(
    [0] => stdClass Object
        (
            [tCategory] => 1
            [tName] => Small
    )

    [1] => stdClass Object
        (
            [tCategory] => 1
            [tName] => Medium
    )
    [2] => stdClass Object
        (
            [tCategory] => 1
            [tName] => Large
    )

)

推荐答案

要使产品行包含列 types ，用第二个表的行填充，您可以将两个表连接在一起，并按以下方式进行分组：

To have a product row contain a column types which is populated with the rows of a 2nd table, you can either join both tables and play with group by:

SELECT 
    product.id, 
    max(product.name) as name, 
    max(product.price) as price, 
    max(product.description) as description, 
    group_concat(product_types.tName) as types

FROM
    product

LEFT JOIN
    product_type ON product.tName = product_types.tCategory

GROUP BY product.id

max（product.name）后面没有魔术。您不得使用选择子句中不在分组子句中或未归类的列。由于product.id是主键，因此每个product.id只会获得一个product.name，因此我们不在乎选择3个product.name（通过3种类型的联接）中的哪个。他们都是一样的。我们甚至可以在选择子句中写any（product.name），但是我不认为mysql支持这一点。 =）

there is no magic behind max(product.name). you are not allowed to use columns in the select-clause that are not in the group by-clause or aggregated. since product.id is the primary key, we will only get one product.name for each product.id, so we don't care which of the 3 product.name (from the join with the 3 types) gets selected. they are all the same. we could even write any(product.name) in the select-clause but i don't think mysql supports this. =)

或执行相关子查询〜

SELECT 
    product.id, 
    product.name, 
    product.price, 
    product.description, 
    (SELECT 
         group_concat(product_types.tName)
     FROM product_types
     WHERE product.tName = product_types.tCategory
     GROUP BY product_types.tCategory
    ) as types

FROM
    product

我建议使用第一个查询，因为mysql优化起来更容易。记录：我没有测试这些查询，因此可能需要进行一些调整。

i suggest to use the first query as it will be easier for mysql to optimize. for the record: i did not test those queries so it's possible they need some tweaking. just let me know.

编辑1：使用max（）的进一步说明

在以下查询中，我们不允许使用列 name ，因为我们仅按列 id 分组。

In the following query we are not allowed to use the column name, because we only grouped by the column id.

SELECT
    id,
    name /* not allowed */

FROM
    product

GROUP BY id

我们只能选择列在组中的子句。我们也可以使用列，但这些列不在组中，子句中，但聚集函数如 max 和 group_concat 。

we may only select columns that are in the group by-clause. we may also use columns, that are not in the group by-clause though aggregate functions like max and group_concat.

要解决此问题，我们只需添加列 name 通过-子句

to solve this problem we can just add the column name to the group by-clause

SELECT
    id,
    name /* ok because it's in group by */

FROM
    product

GROUP BY id, name

如果我们现在对 name 具有不同的值，我们将获得不止一个结果中的元组，例如：

if we now have different values for name, we will get more than one tuple in the result, e.g.:

对于 product 表（id，名称）= {（1，Alice ），（1，Bob）}我们得到结果

For the product table (id, name) = {(1, Alice), (1, Bob)} we get the result

1, Alice
1, Bob

因为我们将这两列都分组了。

because we grouped both columns.

第二种方法是使用汇总函数，例如max：

the 2nd approach is using an aggregate function, like max:

SELECT
    id,
    max(name) /* ok because it's aggregated */

FROM
    product

GROUP BY id

对于产品表（id，name）= {（1，Alice），（1，Bob）}，我们得到结果

For the product table (id, name) = {(1, Alice), (1, Bob)} we get the result

1, Bob /* max(Alice,Bob) = Bob, because A < B */

在您的示例中，我假设列product.id是主键，因此是唯一的。这意味着我们在 name 列中不能有与 id 列中相等的值相同的值。 {（1，Alice），（1，Bob）} 是不可能的，但也许 {（1，Alice），（2，Bob） } 。如果现在我们< GROUP BY product.id ，则对于该组中的每个元组，我们将获得 product.name 的值。但是由于 id 确定了名称，所以这些值都相同：

In your example I assumed that the column product.id is the primary key and therefore unique. This means that we can not have different values in the name column for equal values in the id column. {(1, Alice), (1, Bob)} is not possible, but maybe {(1, Alice), (2, Bob)}. If we GROUP BY product.id now, we get a value for product.name for each tuple in the group. But because the id determines the name, those values are all the same:

SELECT 
    product.id, 
    product.name, 
    product_type.tName,

FROM
    product

LEFT JOIN
    product_type ON product.tName = product_types.tCategory

将导致

(1, "White T-Shirt", Small),
(1, "White T-Shirt", Medium),
(1, "White T-Shirt", Large),
(2, "Black T-Shirt", Small),
(2, "Black T-Shirt", Medium),
(2, "Black T-Shirt", Large)

按product.id分组后，结果将类似于

after grouping it by product.id the result will look like

(1, F("White T-Shirt", "White T-Shirt", "White T-Shirt"), 
     G(Small, Medium, Large)),
(2, F("Black T-Shirt", "Black T-Shirt", "Black T-Shirt"), 
     G(Small, Medium, Large))

其中 F 和 G 是agg regate select 子句中使用的函数。对于 F 来说，无论使用哪个值，都相同。因此我们只使用了 max 。对于g，我们使用 group_concat 来汇总所有值。

where F and G are the aggregate functions used in the select-clause. for F it does not matter which value we use, they are all the same. so we just used max. for g we used group_concat to concat all values together.

因此

F("White T-Shirt", "White T-Shirt", "White T-Shirt") = "White T-Shirt"
F("Black T-Shirt", "Black T-Shirt", "Black T-Shirt") = "Black T-Shirt"
G(Small, Medium, Large) = "Small, Medium, Large"

这将导致

(1, "White T-Shirt", "Small, Medium, Large"),
(2, "Black T-Shirt", "Small, Medium, Large")

这篇关于从两个Mysql表获取信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！