CODEWITHSUNDEEP
mysqlsqllaravel-5
Ziaur Rahman
Ziaur Rahman

Reputation: 1188

How do i union or join multiple tables with multiple where clauses?

I have two tables one is 'member' and another is 'savings'. the member table is below:

id|DomainName|DivisionName|ZoneName|MemberId|GeneralSaving
 1|  hem     | raj        | raj    |1111    |100
 2|  hem     | raj        | bog    |2222    |100
 3|  edu     | dha        | dha    |3333    |100
 4|  edu     | dha        | nara   |4444    |100
 5|  busi    | dha        | gazi   |5555    |100
 6|  busi    | raj        | raj    |6666    |100

and the 'savings' table is as below:

id|DomainName|DivisionName|ZoneName|MemberId|DPS|Month|Year|AccounNo
 1|  hem     | raj        | raj    |1111    |100|jan  |2017|11111
 2|  hem     | raj        | bog    |2222    |150|jan  |2017|22221
 3|  edu     | dha        | dha    |3333    |200|jan  |2017|33331
 4|  edu     | dha        | nara   |4444    |250|jan  |2017|44441
 5|  busi    | dha        | gazi   |5555    |300|jan  |2017|55551

Here, ZoneName is subset of DivisionName, DivisionName is subset of DomainName. Sometimes just DomainName is given, sometimes DomainName and DivisionName are given and Most of the time DomainName, DivisionName, ZoneName these three are given. I need to post the GeneralSaving of 'member' and DPS of 'savings' after joining two tables, obviously i can't post the same month data. A person can have more than two or three account no, though account no is unique. Now How can I join or union of the two table in laravel 5.2 ?

the output will be as below: DomainName = hem, DivisionName=raj, ZoneName=raj, Month=Feb, Year=2017

MemberId|AccountNo|GeneralSaving|DPS
1111    |11111    |100          |100
2222    |22221    |100          |150

if DomainName = busi, DivisionName = dha, Month=Feb, Year=2017

MemberId|AccountNo|GeneralSaving|DPS
5555    |55551    |100          |300
6666    |         |100          |

Upvotes: 0

Views: 56

Answers (2)

Ziaur Rahman
Ziaur Rahman

Reputation: 1188

In laravel 5 the sql of above query will be as follows:

DB::table('member')
    ->leftjoin('savings', 'savings.MemberId', '=', 'member.MemberId')
    ->select('member.MemberId', 'savings.AccountNo', 'member.GeneralSaving', 'savings.Dps')
    ->where('savings.DomainName', '=', $DomainName)
    ->where('savings.DivisionName', '=', $DivisionName)
    ->where('savings.Month', '=', $Month)->where('savings.Year', '=', $Year)
    ->get

This query has been tested in laravel 5 and it has worked

Upvotes: 0

Rohit Gaikwad
Rohit Gaikwad

Reputation: 815

Try this.....

    SELECT s.MemberId, s.AccounNo, s.DPS, m.GeneralSaving
    FROM member m
    JOIN savings s ON CASE WHEN $DomainName != null THEN s.DomainName = $DomainName END 
    AND CASE WHEN $DivisionName != null THEN s.DivisionName = $DivisionName END
    AND CASE WHEN $ZoneName != null THEN s.ZoneName = $ZoneName END

Here $DomainName , $DivisionName, $ZoneName are user inputs.

Upvotes: 1

Related Questions