[SOLVED] Single statement with if-else.

[xenophobia]

Is kinda hard. Just want to see whether it can be achieve or not :-\

 

Okay, here we goes. I got 2 tables:

- customer_company

- customer_user

The customer_user will link to customer_company (foreign key) by the field name "customerID". One customer_company may have 1 or many customer_user.

 

I want to build a sql statement to return me the contact number of this customer. There might be possible where the customer_user did not provided the phone number. In this case, we will return the customer_company's phone number.

 

The logic is like this:

IF customer_user.phone_number == "" THEN

  RETURN customer_company.phone_number;

ELSE

  RETURN customer_user.phone_number;

END IF

 

So how I going to achieve this is one single sql statement? meaning:

$sql = "<THIS IS THE ONLY SINGLE STATEMENT>;";

$qry = mysql_querty($sql) or die(mysql_error());

 

As the php only allowed one statement per query (mysql_query()).

 

Don't ask me why not use multiple statement, I got my reason for that but I lazy to explain... ><

 

You can give me alternative like JOIN statement or whatever you think can be achieve but just in ONE statement.

 

Thanks!

[dreamwest]

$sql = "SELECT customer_user.*, customer_company.* ".
"FROM video, customer_company ". 
";

$result = mysql_query( $sql ); 
$row = mysql_fetch_array( $result );

if ( $customer_user['phone_number'] == "" )
    {
        echo $row['customer_company.phone_number'] ;
        
    }else{
echo $row['customer_user.phone_number'] ;
}

[Ken2k7]

dreamwest, cartesian join is slow. Just saying. You may want to know a little about performance.

[xenophobia]

Thanks for your reply!

 

But I forgot to said, I can't edit the php code. Because I was working on a system whereby the system allowed me to enter a query string which used to retrieve some information from other database. and I believe that this system will use this query string and dump into the mysql_query($str).

 

That's why I can only do this with SQL statement alone. I've checked on MYSQL doc, you can do something:

 

SELECT IF(1>2, 'yes', 'no') AS 'result';

 

But I just can't do:

 

SELECT IF(SELECT COUNT(*) FROM customer_company WHERE phone<>'', SELECT phone_number FROM customer_company, SELECT phone_number FROM customer_user) AS 'result';

 

T.T Thanks in advanced.

[Ken2k7]

When you said customerID links to customer_company, I assume customer_company has an ID field? If so, try this -

 

SELECT IF (cu.phone_number = '', cc.phone_number, cu.phone_number) AS phone
FROM customer_user cu
INNER JOIN customer_company cc
     ON cu.customerID = cc.id

[dreamwest]

dreamwest, cartesian join is slow. Just saying. You may want to know a little about performance.

 

Isnt it more flexible?

 

I mean its query based so you can limit, sort etc

[xenophobia]

When you said customerID links to customer_company, I assume customer_company has an ID field? If so, try this -

 

SELECT IF (cu.phone_number = '', cc.phone_number, cu.phone_number) AS phone
FROM customer_user cu
INNER JOIN customer_company cc
     ON cu.customerID = cc.id

 

Hey, that's work! Thanks. You are great.