I am using Codeigniter and I am trying to call info from two tables:
Product: id, name, price, description, typeId
Product_Type: tCategory, tName
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:
Product 1 will be a red shirt for $20 and from type I will need Large, Medium and Small.
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.
Here is my code:
$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.
EDIT:
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
)
)
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
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. =)
or do a correlated sub-query ~
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
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.
Edit1: Further explanation for using max()
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
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.
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
if we now have different values for name, we will get more than one tuple in the result, e.g.:
For the product table (id, name) = {(1, Alice), (1, Bob)} we get the result
1, Alice
1, Bob
because we grouped both columns.
the 2nd approach is using an aggregate function, like max:
SELECT
id,
max(name) /* ok because it's aggregated */
FROM
product
GROUP BY id
For the product table (id, name) = {(1, Alice), (1, Bob)} we get the result
1, Bob /* max(Alice,Bob) = Bob, because A < B */
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
will result in
(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)
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))
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.
therefore
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"
this will result in
(1, "White T-Shirt", "Small, Medium, Large"),
(2, "Black T-Shirt", "Small, Medium, Large")