Replacing a column with another!

discostudio

Hi,

I have a table with the following:

upn|parent_upn|description|etc etc

What I want to do is, when the parent_upn is not Zero, I want it to select the parent_upn as the upn column.

I was thinking something like this:

SELECT IF(`parent_upn` != '0', `parent_upn` AS `upn`)
ELSE(`upn` AS `upn`) 
FROM `products` WHERE ......

This doesn't work, what am I doing wrong.

nnichols

Almost there -

SELECT IF(parent_upn, parent_upn, upn) AS upn
FROM products ...

Have a look here dev.mysql.com

Hope this helps 😉

discostudio

Nope, that didn't work. - Error in SQL syntax:

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM Product_Prices AS `p` INNER JOIN Product_Category_Links AS `c` ON (' at line 1

QUERY = SELECT IF(parent_upn, parent_upn, upn) AS upn, * FROM Product_Prices AS `p` INNER JOIN Product_Category_Links AS `c` ON (`p`.`partcode` = `c`.`partcode`) WHERE `c`.`cat_id` = '2403' AND `p`.`parent_partcode` = '0' ORDER BY `p`.`trade_price` ASC, `p`.`prod_name` ASC, `p`.`prod_title` ASC

nnichols

You cannot use the IF() construct followed by *, but it works fine if you name the columns explicitly (which is good practice anyway). This appears to be a bug / feature of MySQL, but I haven't done an in depth search of the documentation.

mjax

Try this:

SELECT IF(parent_upn <> 0, parent_upn, upn) AS upn
FROM products ...

discostudio

No, still doesn't work, Error in SQL query.

discostudio

Does the upn and parent_upn have to be an integer? - Can they contain letters?

nnichols

I just ran it against a text field and it worked fine. Did you change the * to a field list?

discostudio

Yeh look:

SELECT IF(parent_partcode, parent_partcode, partcode) AS `p`.`partcode`, 
	`p`.`parent_partcode`,  `p`.`prod_title`, `p`.`prod_name`, `p`.`prod_tag` 
	FROM " . $glob['table_products'] . " AS `p` 	
	INNER JOIN " . $glob['table_categories_idx'] . " AS `c` ON (`p`.`partcode` = `c`.`partcode`) 
	WHERE...

I have also tried:

parent_partcode > 0

and

parent_partcode <> 0

parent_partcode != 0

to no luck and tried doing a "p.parent_partcode" and all other fields to make sure there is no ambiuation.

discostudio

Anyone else any ideas? - I'm getting quite desperate to get this finished.

Roger_Ramjet

Well, you can use a union query for this

SELECT upn, description FROM products WHERE parent_upn = 0
UNION
SELECT parent_upn, description FROM products WHERE parent_upn != 0

Simplest solution that will work. Remember, second select will inherit column names from first select.

nnichols

What error are you getting when you try to run the query above in the mysql client? If you're still having problems with this can you post the full table structure for the tables involved?

discostudio

Just " error in MySQL syntax '.partcode,
p.parent_partcode, p.prod_title, p.prod_name, p.prod_tag' "

No Idea what is going on!

nnichols

That particular error is being caused by you trying to prefix an alias with a table reference.

The first line of your query should be -

SELECT IF(parent_partcode, parent_partcode, partcode) AS `partcode`,

not

SELECT IF(parent_partcode, parent_partcode, partcode) AS `p`.`partcode`,

If you are still unable to get it to work please post the full query and mysql error here.

discostudio

1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`partcode`, `p`.`parent_partcode`, `p`.`prod_title`, `p`.`prod_name`, `p`.`' at line 2

QUERY = SELECT IF (`parent_partcode`,`parent_partcode`, `partcode`) AS p.`partcode`, `p`.`parent_partcode`, `p`.`prod_title`, `p`.`prod_name`, `p`.`prod_tag` FROM Product_Prices AS `p` INNER JOIN Product_Category_Links AS `c` ON (`p`.`partcode` = `c`.`partcode`) WHERE `c`.`cat_id` = '2429' AND `p`.`parent_partcode` = '0' ORDER BY `p`.`trade_price` ASC, `p`.`prod_name` ASC, `p`.`prod_title` ASC

and:

1052: Column 'partcode' in field list is ambiguous

QUERY = SELECT IF (`parent_partcode`,`parent_partcode`, `partcode`) AS `partcode`, `p`.`parent_partcode`, `p`.`prod_title`, `p`.`prod_name`, `p`.`prod_tag` FROM Product_Prices AS `p` INNER JOIN Product_Category_Links AS `c` ON (`p`.`partcode` = `c`.`partcode`) WHERE `c`.`cat_id` = '2429' AND `p`.`parent_partcode` = '0' ORDER BY `p`.`trade_price` ASC, `p`.`prod_name` ASC, `p`.`prod_title` ASC

discostudio

and I have just tried removing the space between the if and open bracket:

IF(

to no avail?

nnichols

But you did not remove the table prefix from the column alias as I suggested in my previous post.

Sorry just re-read it.

nnichols

SELECT IF (`parent_partcode`,`parent_partcode`, `p`.`partcode`) AS `partcode`,
	`p`.`parent_partcode`,
	`p`.`prod_title`,
	`p`.`prod_name`,
	`p`.`prod_tag`
FROM Product_Prices AS `p`
INNER JOIN Product_Category_Links AS `c` ON (`p`.`partcode` = `c`.`partcode`)
WHERE `c`.`cat_id` = '2429'
AND `p`.`parent_partcode` = '0'
ORDER BY `p`.`trade_price` ASC, `p`.`prod_name` ASC, `p`.`prod_title` ASC

1052: Column 'partcode' in field list is ambiguous

This error refers to the partcode within the IF()

discostudio

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT IF (parent_partcode,parent_partcode, p.partcode) AS partcode, ' at line 1

I can usually easily understand mySQL functions but this one is baffling me.

nnichols

Check your PMs