JOIN Multiple tables

You need to evaluate whether this is a Laravel question or a SQL question, and then whether you actually need a join.

To join them in sql (you would need to have use for columns on multiple tables) you would have something like

SELECT  Items.*, Credit_sales.* from Items LEFT JOIN Credit_sales on Items.id = Credit_sales.id

But from what you said you could just

SELECT * from Credit_sales where item_id = 3

Now - this isn't Laravel specific though. I would advise that you read the ORM documentation, all the way through, where I imagine you would end up with code similar to:

$item = Item::find(13);

foreach ($item->creditSales as $creditSale) {
    $creditSale->foo();
}

OR, simply

$creditSales = CreditSale::where('item_id', 13);

foreach ($item->creditSales as $creditSale) {
    $creditSale->foo();
}

Either way - your question needs more context in order to be able to help.

Why don't you have a go at the solution and then ask for help rather than expecting someone to write it for you?

Its a fairly simple Join of three tables and a select statement.

i tried this on sql, but i don't know if is correct or not how i can put in laravel

                  SELECT c.name,a.`vch_no`,b.vch_no,a.quantity,b.quantity,a.rate,b.rate,b.rate,
               a.amount,b.amount,d.quantity,d.rate,d.amount,d.created_at,a.created_at,b.created_at
              FROM credit_sales a, cash_sales b, products c ,itemstocks d
            WHERE a.`item_id` = c.id
            AND b.`item_id` = c.id
           AND d.`item_id` = c.id
            AND c.id=4

@emfinanga

Try this.

Creditsales::select("product.name, credit_sales.vch_no, cash_sales.vch_no, credit_sales.quantity, cash_sales.quantity, credit_sales.rate, cash_sales.rate, cash_sales.rate,  credit_sales.amount, cash_sales.amount, itemstocks.quantity, itemstocks.rate, itemstocks.amount, itemstocks.created_at, credit_sales.created_at, cash_sales.created_at")
    ->where('product.id', 4)
    ->join('products', 'credit_sales.item_id', '=', 'products.id')
    ->join('cash_sales', 'products.id', '=', 'cash_sales.item_id')
    ->join('itemstocks', 'products.id', '=', 'itemstocks.item_id')
    ->get();

I haven't tested your code. you may need to tweak.

have you made a positive decision to not use Eloquent or have you suggested a join as its the only way you know?

@snapey , i need the simply way and correct so you can help me

on my model i used like this

                   class CashSales extends Model
           {

       protected $fillable = [
         'vch_no',
        'customer_cash_id',
        'item_id',
        'store_id',
        'quantity',
        'rate',
      'amount'];

        public function customer_cash(){

      return $this->belongsTo('App\Customer_cash','customer_cash_id','id');
      }

     public function items(){

       return $this->belongsTo('App\Products','item_id','id');
      }

        public function store(){

        return $this->belongsTo('App\Store','store_id','id');
      }
    }



      

class Itemstock extends Model { protected $fillable = [

    'item_id',
      'store_id',
     'quantity',
      'rate',
     'per',
    'amount'];

    public function Items(){

    return $this->belongsTo('App\Products','item_id','id');
     }


   public function store(){
    return $this->hasOne('App\Store','id','store_id');
     }


         }



 class Products extends Model
 {
  protected $fillable = [ 'name', 'category_id'];


  public function category(){
    return $this->hasOne('App\Category','id','category_id');
  }

  }

@tisuchi is not working i got the error

       SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'cash_sales' (SQL: select 
              `product`.`name`, `credit_sales`.`vch_no`, `cash_sales`.`vch_no`, `credit_sales`.`quantity`, 
      `cash_sales`.`quantity`, `credit_sales`.`rate`, `cash_sales`.`rate`, `cash_sales`.`rate`, ` 
      credit_sales`.`amount`, `cash_sales`.`amount`, `itemstocks`.`quantity`, `itemstocks`.`rate`, 
       `itemstocks`.`amount`, `itemstocks`.`created_at`, `credit_sales`.`created_at`, 
  `cash_sales`.`created_at` from `cash_sales` inner join `products` on `credit_sales`.`item_id` = 
     `products`.`id` inner join `cash_sales` on `products`.`id` = `cash_sales`.`item_id` inner join 
  `itemstocks` on `products`.`id` = `itemstocks`.`item_id` where `product`.`id` = 4)

@emfinanga

So, what is the problem with your current relationship?

@tisuchi

There is no problem, but i want to Evaluate one items by join those tables.

Example items 4 I want to know how many qnty in ward and out ward.