Hi,
I really need some help. I'm trying to use an IF statement in a multi-table
left join. I'm joining four tables (customers, contents, courses &
locations).
In the contents table I have two fields, product_id and value_id that relate
to different values in different tables depending upon the value in the
product_type field. If product_type='course' then the product_id and
value_id relate to the courses and locations table, but if
product_type='other' then the product_id and value_id relate to the product
and prices table.
I have two options:
1. Match only the courses and locations where product_type='course' and
NULL values if relating to the product and prices table.
2. Or change the query (and fields) if the product_type='other' to use
the product and prices table.
I'm using Mysql 4.3. Here's what I had that worked, but it did not take
into account that the values in the product_id and value_id fields relate to
different tables if the product_type != 'course'...
$command = " CREATE TABLE $table[name] ";
$command.= " SELECT $fields1, $fields2, $fields3 ";
$command.= " FROM (($table[customers] AS c ";
$command.= " LEFT JOIN $table[contents] AS o ON o.order_id = c.order_id)
";
$command.= " LEFT JOIN $table[courses] AS t ON o.product_id = t.id) ";
$command.= " LEFT JOIN $table[locations] AS l ON o.value_id = l.id ";
Here's my first attempt at employing the IF statement within the Mysql
query:
$command = " CREATE TABLE $table[name] ";
$command.= " SELECT $fields1, $fields2, $fields3 ";
$command.= " FROM ($table[customers] AS c ";
$command.= " LEFT JOIN $table[contents] AS o ON o.order_id = c.order_id)
";
$command.= " IF((o.product_type='course'), (LEFT JOIN $table[courses] AS
t ON o.product_id = t.id LEFT JOIN $table[locations] AS l ON o.value_id =
l.id), NULL )";
Finally, if there is a way for me to override a column value using PHP
during the execution of the mysql query, then I could accept a simpler
solution. If I can use PHP to check the value of product_type and then get
the values of product_id and value_id from an array... All I need to know
is how to inject php values into my simple query. Say:
SELECT field1, field2, field3, IF(o.product_type='course',
$product_name[$product_id] ,NULL) AS product, field5 FROM table1 as a,
table2 as o;
The key point here is that I must be able to output the final results of my
query to a new table.
I'd be extremely grateful for any help anyone can provide. Thanks.
Keith
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
