IF Clause in Left Join

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

Re: IF Clause in Left Join

> 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).

IF can only be used in the select part or the where part, it can't be
used in the join part of a query.

> 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.

You can union the results.

select ... where product_type='course'
union
select ... where product_type='other'

> I'm using Mysql 4.3.

Didn't know there was such a version ;)

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php