joins - columns with same name in result set

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

joins - columns with same name in result set

Post by Luke »

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 :D ) 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:

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
		100
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:
desired result set wrote: [id] => 1797
[name] => Stella Tote/Pack Small
[thumbnail] => graphics/products/ELL/thumbs/ELL001C.jpg
[image] => graphics/products/ELL/ELL001C.jpg

Code: 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.jpg

Code: Select all

=> ELL001C
            [value] => Pearl


            [id] => 1797
            [name] => Stella Tote/Pack Small
            [thumbnail] => graphics/products/ELL/thumbs/ELL001C.jpg
            [image] => graphics/products/ELL/ELL001C.jpg

Code: 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.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

On first reading I would say it is bringing back 2 records because there are 2 related records in Product values table related to 1 in the product table.

Also you are using a left join to join Products to Product Values (which would bring back all records in ALL Products and records from Product Values table where there is a relation). But by virtue of the fact that you then limit Product Values records in the where clause (IN (5,1)) would mean that only records with a relation will ever come back. i.e. not ALL records from Product table will come back.

Basically write it as an INNER JOIN as the mysql optimiser will do that anyway based on the query you've posted. Dont give the optimiser more work that it has to do.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

As much as I hate to admit it, I really don't know what I'm doing when it comes to more than simple select, update, delete, etc. queries. I am actually scouring amazon right now looking for a mysql book that would be useful to somebody like myself who has had years of experience with mysql, but has always avoided (for some reason) actually learning it properly. :oops:

Recommendations welcome!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Because of the normalization, it's not possible to really mash them together. You'll just need some logic to handle it.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: joins - columns with same name in result set

Post by Christopher »

The Ninja Space Goat wrote: 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:
desired result set wrote: [id] => 1797
[name] => Stella Tote/Pack Small
[thumbnail] => graphics/products/ELL/thumbs/ELL001C.jpg
[image] => graphics/products/ELL/ELL001C.jpg

Code: Select all

=> ELL001C
            [value-color] => Pearl
            [value-manufacturer] => ELL[/quote]

[/quote]
You want to explicitly specify the columns you want returned and use AS:
[syntax="sql"]
	SELECT
		s01_Products.name,s01_Products.thumbnail, ... s01_CFM_ProdValues.value AS value-manufacturer
[/syntax]
(#10850)
Post Reply