CODEWITHSUNDEEP
phpmysqllaravellaravel-5eloquent
Test Coder
Test Coder

Reputation: 37

Multi table Mysql joint query in Laravel

Am trying to connect two tables Customer and Customer_Usage for getting the result.Tables are showing below.

Table: Customer

+----+-------+-------------+--------+
| id | Ip_Id | Customer_Id | Class  |
+----+-------+-------------+--------+
| 1  | 100   | A           | First  |
| 2  | 100   | B           | First  |
| 3  | 100   | C           | First  |
| 4  | 101   | D           | First  |
| 5  | 101   | E           | First  |
| 6  | 100   | F           | Second |
+----+-------+-------------+--------+

Table: Customer_Usage

+----+-------------+----------------+
| id | Customer_Id | Usage          |
+----+-------------+----------------+
| 1  | A           | 1245           |
| 2  | B           | 4414           |
| 3  | C           | 8521           |
| 4  | D           | 2314           |
| 5  | E           | 521            |
| 6  | F           | 5412           |
+----+-------------+----------------+

The condition is while enter a value for search Ip_Id, example for 100 it will give a result like below. How to make a joint for these two tables in Laravel using Mysql

Output result

+-------+-------------+----------------+
| Ip_Id | Customer_Id | Usage          |
+-------+-------------+----------------+
| 100   | A           | 1245           |
| 100   | B           | 4414           |
| 100   | C           | 8521           |
| 100   | F           | 5412           |
+-------+-------------+----------------+

This is the query am trying. 

 $result        = DB::table('Customer')
                ->where('Ip_Id','=','100')
                ->get();

Upvotes: 1

Views: 144

Answers (2)

Sandeep Modak
Sandeep Modak

Reputation: 842

Left join is recommended to achieve what you need. 

DB::table('Customer')
            ->select('Customer.Ip_Id','Customer.Customer_Id','Customer_Usage.Usage')
            ->leftJoin('Customer_Usage', 'Customer_Usage.Customer_Id', '=', 'Customer.Customer_Id')
            ->where('Customer.Ip_Id',100)
            ->get();

Upvotes: 1

Lijesh Shakya
Lijesh Shakya

Reputation: 2540

Use Eloquent Inner Join like:

$results  = DB::table('Customer')
->select('Customer.Ip_Id','Customer.Customer_Id', 'Customer_Usage.Usage')
->join('Customer_Usage','Customer.Customer_Id', '=','Customer_Usage.Customer_Id')
->where('Ip_Id', 100)                
->get();

You will get the desired output like above.

Upvotes: 0

Related Questions