Multiple LeftJoin Editor Problem

Multiple LeftJoin Editor Problem

vincmeistervincmeister Posts: 136Questions: 36Answers: 4
edited April 2016 in Free community support

Hello Allan,

Improving my question here , I have a problem with multiple leftJoin

Parent table: tr_receiving_head.

I have a child table tr_receiving_detail, with field
id, tr_in_id, item_code, customer_code

and I want to get :
- item_name (from master_item table) using item_code
- customer_name (from master_customer table) using customer_code

I'm trying to create multiple left join
This one is working

->leftJoin( 'tr_receiving_head', 'tr_receiving_head.tr_in_id', '=', 'tr_receiving_detail.tr_in_id' )
->leftJoin( 'master_item', 'master_item.item_code', '=', 'tr_receiving_detail.item_code' )

This one is not working, the result are duplicate

->leftJoin( 'tr_receiving_head', 'tr_receiving_head.tr_in_id', '=', 'tr_receiving_detail.tr_in_id' )
->leftJoin( 'master_item', 'master_item.item_code', '=', 'tr_receiving_detail.item_code' )
->leftJoin( 'master_customer', 'master_customer.customer_code', '=', 'tr_receiving_detail.customer_code' )

if i'm using MySQL query, GROUP BY tr_receiving_detail.id will make the result correct. But i read that editor not support GROUP BY.

Please advise, thank you

Replies

  • allanallan Posts: 63,851Questions: 1Answers: 10,519 Site admin

    Hi,

    You are correct - unfortunately there is no option to use GROUP BY in Editor at the moment because of the complications that it adds for editing the data.

    At the moment, the only way to do that is to create a VIEW and then have Editor read the data from that view.

    Allan

  • vincmeistervincmeister Posts: 136Questions: 36Answers: 4

    Hi Allan,

    Thank you for the respond. I think i'll use another way.

This discussion has been closed.