Column to use?

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.

iNFERiON

Hmm, not too familiar with the MySQL If/Else syntaxes, but it seems that you are missing a THEN and END IF statement.

I guess (but not sure as I never used it before) it should be something like this:

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

You can read more about it in the MySQL Manual.

You could also try this to achieve what you want.

$query = "SELECT `parent_upn`, `upn` FROM `products` WHERE ....";
$assoc = mysql_fetch_assoc($query);
if ($assoc['parent_upn']!="0"); {
  $query2 = "SELECT `parent_upn` AS `upn` FROM `products` WHERE ....";
} else {
  $query2 = "SELECT `upn` AS `upn` FROM `products` WHERE ....";
}

Bit more work, but should do the trick aswell.

ednark

one solution comes to mind with a UNION :
You do two queries at once, and as long as the columns match up you can just ask for both results back together with the UNION statment.

the column names come from the first query so you don't technically need the AS in the second query

You can even put an order by outside the selects to order they whole #! (shebang).

(
  SELECT
    upn
  FROM 
    table 
  WHERE
    parent != 0
) UNION (
  SELECT
    upn_parent as upn
  FROM 
    table 
  WHERE
    parent = 0
) 
ORDER BY upn

This is however not an ideal solution as the database will be making two passes on the table and will actually have had all the information you need on the first pass. perhaps you can do better with the IF statment if you are using mysql5