SQL query issue

LukeDouglas
Topic Author
Offline

7 years 10 months ago #300532 by LukeDouglas

SQL query issue was created by LukeDouglas

-- HikaShop version -- : 4.0.0
-- Joomla version -- : 3.9.0
-- PHP version -- : 7.2
-- Browser(s) name and version -- : various
-- Error-message(debug-mod must be tuned on) -- : #1054 - Unknown column 'tqhsa_users.name' in 'field list'

I am trying to get an export file to send to the client of information they need. I have been able to get the order file with certain fields export. However, now they want additional information from other tables such as the product name and the users name.

When I run my query, I get this error. However, the jos_users.name is a valid column name. Any ideas what is wrong?

Code:

SQL query: Documentation

SELECT jos_hikashop_order.order_number, 
jos_hikashop_order.order_created,
jos_hikashop_order.order_invoice_number,
jos_hikashop_order.order_invoice_created,
jos_hikashop_product.product_name, 
jos_hikashop_order.order_full_price,
jos_hikashop_order.order_discount_code,
jos_hikashop_order.order_discount_price,
jos_hikashop_order.order_payment_method,
jos_hikashop_order.order_payment_price,
jos_hikashop_order.order_partner_id,
jos_users.name,
jos_hikashop_order.order_partner_price,
jos_hikashop_order.order_partner_paid 

FROM jos_hikashop_order_product

LEFT JOIN jos_hikashop_order 
ON jos_hikashop_order.order_id = jos_hikashop_order_product.order_id 

LEFT JOIN jos_hikashop_product 
ON jos_hikashop_order_product.order_product_id = jos_hikashop_product.product_id

LEFT JOIN jos_hikashop_user
ON jos_hikashop_order.order_partner_id = jos_hikashop_user.user_partner_id LIMIT 0, 25
MySQL said: Documentation

#1054 - Unknown column 'jos_users.name' in 'field list'

Please Log in or Create an account to join the conversation.

nicolas
Offline

7 years 10 months ago #300536 by nicolas

Replied by nicolas on topic SQL query issue

Hi,

You're missing a LEFT JOIN to the jos_users table with an ON jos_users.id = jos_hikashop_user.user_cms_id

Please Log in or Create an account to join the conversation.

LukeDouglas
Topic Author
Offline

7 years 10 months ago #300576 by LukeDouglas

Replied by LukeDouglas on topic SQL query issue

Nicolas,

Thanks. That got the SQL query to execute. I now get the users (partners) name displayed but the product name is not displaying.

SQL query:

Code:

SELECT jos_hikashop_order.order_number, 
jos_hikashop_order.order_created,
jos_hikashop_order.order_invoice_number,
jos_hikashop_order.order_invoice_created,
jos_hikashop_product.product_name, 
jos_hikashop_order.order_full_price,
jos_hikashop_order.order_discount_code,
jos_hikashop_order.order_discount_price,
jos_hikashop_order.order_payment_method,
jos_hikashop_order.order_payment_price,
jos_hikashop_order.order_partner_id,
jos_users.name,
jos_hikashop_order.order_partner_price,
jos_hikashop_order.order_partner_paid 

FROM jos_hikashop_order_product

LEFT JOIN jos_hikashop_order 
ON jos_hikashop_order.order_id = jos_hikashop_order_product.order_id 

LEFT JOIN jos_hikashop_product 
ON jos_hikashop_order_product.order_product_id = jos_hikashop_product.product_id

LEFT JOIN jos_hikashop_user
ON jos_hikashop_order.order_partner_id = jos_hikashop_user.user_partner_id 

LEFT JOIN jos_users ON jos_users.id = jos_hikashop_user.user_cms_id;

Can you see the issue? I can't.

Please Log in or Create an account to join the conversation.

Jerome
Offline
HikaShop core developer

7 years 10 months ago #300578 by Jerome

Replied by Jerome on topic SQL query issue

Hello,

It's not the "order_product_id" but the "product_id".
And you should use directly the order_product_name, it will be easier and require one less join.

Regards,

Jerome - Obsidev.com
HikaMarket & HikaSerial developer / HikaShop core dev team.

Also helping the HikaShop support team when having some time or couldn't sleep.
By the way, do not send me private message, use the "contact us" form instead.

Please Log in or Create an account to join the conversation.

LukeDouglas
Topic Author
Offline

7 years 10 months ago #300628 by LukeDouglas

Replied by LukeDouglas on topic SQL query issue

Jerome,

I didn't see the product name. Thanks!

This SQL worked fine.

Code:

SELECT jos_hikashop_order.order_number, jos_hikashop_order.order_created,jos_hikashop_order.order_invoice_number,jos_hikashop_order.order_invoice_created,jos_hikashop_order_product.order_product_name,jos_hikashop_order.order_full_price,jos_hikashop_order.order_discount_code,jos_hikashop_order.order_discount_price,jos_hikashop_order.order_payment_method,jos_hikashop_order.order_payment_price,jos_hikashop_order.order_partner_id,jos_users.name,jos_hikashop_order.order_partner_price,jos_hikashop_order.order_partner_paid FROM jos_hikashop_order_product LEFT JOIN jos_hikashop_order 
ON jos_hikashop_order.order_id = jos_hikashop_order_product.order_id LEFT JOIN jos_hikashop_user ON jos_hikashop_order.order_partner_id = jos_hikashop_user.user_partner_id LEFT JOIN jos_users ON jos_users.id = jos_hikashop_user.user_cms_id;

Please Log in or Create an account to join the conversation.

Time to create page: 0.196 seconds