Mysql query for listing field values from one table to another

I have the following mysql query:

SELECT order_id, CONCAT(firstname, ' ', lastname) AS customer, shipping_code, total, currency_code, currency_value, date_added, date_modified FROM oc_order` o LEFT JOIN (select komercijalista from oc_customer (order.customer_id = customer.customer_id))


I am trying to list values from komercijalista field (which belongs to oc_customer table) in oc_order table. I am trying to connect them by customer_id which both have, but I am failing at left join. Any suggestions, please? Apart from learning mysql better, which I am already trying to do and this is where I need your help. Thanx.

komercijalista

oc_customer

oc_order

customer_id

Help us help you - please share the tables structures, some sample data and the result you're trying to get
– Mureinik
Jun 29 at 10:39

2 Answers
2

SELECT o.order_id, CONCAT(o.firstname, ' ', o.lastname) AS customer, o.shipping_code, o.total, o.currency_code, o.currency_value, o.date_added, o.date_modified ,b.komercijalista FROM `oc_order` o left join (select komercijalista from oc_customer) b on o.order_id = b.customer.order_id


try this

Hi @p.ganesh I get errors with this query "unknown column o.order_id" and for all other columns with prefix o and b. I do not know how to use left join with my current query. If left join is the right thing to use.
– Nancy
Jun 29 at 11:03

In which table does order_id columns exists,does order _id column exists in oc_order table or not ,please provide some data ,now again copy that query and run
– p.ganesh
Jun 29 at 12:33

order_id exists in both tables (table oc_customer and table oc_order). I have komercijalista field in oc_customer only, which is why i need to join two tables in order to display komercijalista field values.
– Nancy
Jun 29 at 12:40

CORRECTION: customer_id is in both oc_customer table and oc_order table. My bad, sorry! I will correct my question.
– Nancy
Jun 29 at 12:59

I changed to customer_id in query,but i get this error - Unknown column 'b.customer_id' in 'on clause'.
– Nancy
Jun 29 at 13:13

Thanx to @p.ganesh, I managed to figure out how to write the query. In case someone needs this, here is the solution:

SELECT order_id, CONCAT(firstname, ' ', lastname) AS customer, shipping_code, total, currency_code, currency_value, date_added, date_modified FROM oc_order` o LEFT JOIN (select komercijalista from oc_customer (order.customer_id = customer.customer_id))


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.