Need to build a 4 table joint

[kokoyako]

Hi,

 

First, sorry for my low level in english.

 

Here is the original request :

 

$orders_query_raw = "select o.orders_id, o.customers_name, o.customers_country, o.currency, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";

 

It enables me to extract a chart made of 3 tables :

TABLE_ORDERS with fields such as orders_id, customers_name etc...

TABLE_ORDERS_TOTAL with fields such as orders_total_id, text, etc...

TABLE_ORDERS_STATUS with fields such as orders_status_id, languages_id, orders_status_name

 

 

I would like to add a fourth table in the request :

TABLE_CMCIC_REFERENCE with fields such as orders_id et ref_id, in the view to extract ref_id.

 

I tryied to built a new request taht is the following :

 

$orders_query_raw = "select cm.ref_id, o.orders_id, o.customers_name, o.customers_country, o.currency, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_CMCIC_REFERENCE . " cm on (o.orders_id = cm.orders_id), " . TABLE_ORDERS_TOTAL . " ot on (cm.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";

 

I have an error message saying :

 

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 'on (cm.orders_id = ot.orders_id), orders_status s where o.orders

 

select count(*) as total from orders o left join cmcic_reference cm on (o.orders_id = cm.orders_id), orders_total ot on (cm.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '4' and ot.class = 'ot_total'

 

Who could help me solve this problem ?

 

Bests regards.

[Jan Zonjee]

$orders_query_raw = "select cm.ref_id, o.orders_id, o.customers_name, o.customers_country, o.currency, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_CMCIC_REFERENCE . " cm on (o.orders_id = cm.orders_id), " . TABLE_ORDERS_TOTAL . " ot on (cm.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";

I have an error message saying :

 

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 'on (cm.orders_id = ot.orders_id), orders_status s where o.orders

When you use a comma for joining tables you use the where clause for the join (like , " .TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id). For a left join you use the on clause.

This will probably not give an error (but maybe also not a good result):

$orders_query_raw = "select cm.ref_id, o.orders_id, o.customers_name, o.customers_country, o.currency, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_CMCIC_REFERENCE . " cm on (o.orders_id = cm.orders_id) left join " . TABLE_ORDERS_TOTAL . " ot on (cm.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";

[kokoyako]

When you use a comma for joining tables you use the where clause for the join (like , " .TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id). For a left join you use the on clause.

This will probably not give an error (but maybe also not a good result):

$orders_query_raw = "select cm.ref_id, o.orders_id, o.customers_name, o.customers_country, o.currency, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_CMCIC_REFERENCE . " cm on (o.orders_id = cm.orders_id) left join " . TABLE_ORDERS_TOTAL . " ot on (cm.orders_id = ot.orders_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";

 

 

Thank you so much. I copied/pasted the proposed code.

 

I get this message :

 

1054 - Unknown column 'cm.orders_id' in 'on clause'

 

select count(*) as total from orders o left join cmcic_reference cm on (o.orders_id = cm.orders_id) left join orders_total ot on (cm.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '4' and ot.class = 'ot_total'

[Jan Zonjee]

1054 - Unknown column 'cm.orders_id' in 'on clause'

The most straightforward explanation is that there is no field orders_id in that table. You are sure you didn't name it order_id or something like that?

[kokoyako]

Thank you so much. I copied/pasted the proposed code.

 

I get this message :

 

1054 - Unknown column 'cm.orders_id' in 'on clause'

 

select count(*) as total from orders o left join cmcic_reference cm on (o.orders_id = cm.orders_id) left join orders_total ot on (cm.orders_id = ot.orders_id), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '4' and ot.class = 'ot_total'

 

Shame on me. That's much better !

 

But there is an other problem : in cmcic_reference table, order_id is a series of number including hole (1001, 1003, 1004, 1006, etc...). The holes represent people paying by checks (1002 and 1005). The resulat is the following : I have the cic numbers except the "0" so that my final chart is buit without the orders paid by checks.

 

How could I fix this ?