joins - columns with same name in result set
Posted: Mon Feb 26, 2007 5:58 pm
Sorry for the n00bish question, but I'm still pretty green on SQL because it's one of those things like mod_rewrite and regex (getting decent at both of those now
) that I've put off for far too long. Anyway, here's my question:
I have three tables that relate to eachother.
Products
id,
name,
code,
image,
thumbnail,
description
Custom_ProdFields
id,
code,
name
Custom_ProdValues
field_id,
product_id,
value
The product table is the basic product information every product has. The other two tables extend the first table and allow any number of additional product fields. There are two additional "custom" field values with the field_ids of 1 and 5 I need to retrieve with my select from the products table. This data will come from the Custom_ProdValues table, but it relates to the Products table through the ProdFields table. So, I need to do something like:
When I run this query, it does exactly what I need except that because the two extra values coming out of ProdValues both have the column name "value", it seperates them into two results. How would I write this query to return results like this:
I have three tables that relate to eachother.
Products
id,
name,
code,
image,
thumbnail,
description
Custom_ProdFields
id,
code,
name
Custom_ProdValues
field_id,
product_id,
value
The product table is the basic product information every product has. The other two tables extend the first table and allow any number of additional product fields. There are two additional "custom" field values with the field_ids of 1 and 5 I need to retrieve with my select from the products table. This data will come from the Custom_ProdValues table, but it relates to the Products table through the ProdFields table. So, I need to do something like:
Code: Select all
SELECT
*
FROM
`s01_Products`
LEFT JOIN
`s01_CFM_ProdValues`
ON
`s01_CFM_ProdValues`.`product_id` = `s01_Products`.`id`
WHERE
`code`
REGEXP
'^[A-Z]{3}[0-9]+([A-Z]{2}|[B-Z])$'
AND
`s01_CFM_ProdValues`.`field_id`
IN
(5,1)
LIMIT
100desired result set wrote: [id] => 1797
[name] => Stella Tote/Pack Small
[thumbnail] => graphics/products/ELL/thumbs/ELL001C.jpg
[image] => graphics/products/ELL/ELL001C.jpgCode: Select all
=> ELL001C [value-color] => Pearl [value-manufacturer] => ELL[/quote] Instead of returning two seperate arrays like it currently is: [quote="current result set"] [id] => 1797 [name] => Stella Tote/Pack Small [thumbnail] => graphics/products/ELL/thumbs/ELL001C.jpg [image] => graphics/products/ELL/ELL001C.jpgCode: Select all
=> ELL001C [value] => Pearl [id] => 1797 [name] => Stella Tote/Pack Small [thumbnail] => graphics/products/ELL/thumbs/ELL001C.jpg [image] => graphics/products/ELL/ELL001C.jpgCode: Select all
=> ELL001C [value] => ELL[/quote] I apologize for the horrible explanation. I reworded this like 10 times and I can't come up with a better way to ask.