SQL query issue

  • Posts: 332
  • Thank you received: 6
6 years 1 week ago #300532

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

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.

  • Posts: 82863
  • Thank you received: 13372
  • MODERATOR
6 years 1 week ago #300536

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.

  • Posts: 332
  • Thank you received: 6
6 years 1 week ago #300576

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


SQL query:
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.

  • Posts: 26158
  • Thank you received: 4028
  • MODERATOR
6 years 1 week ago #300578

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.

  • Posts: 332
  • Thank you received: 6
6 years 6 days ago #300628

Jerome,

I didn't see the product name. Thanks!

This SQL worked fine.

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.059 seconds
Powered by Kunena Forum