CODEWITHSUNDEEP
mysqlsqljoin
Dileep Kumar
Dileep Kumar

Reputation: 510

mysql join based on 2 columns

How to join 2 tables based on combining 2 columns

Table 1

Name       Bill No    Amount
----      ----------  --------
Ravi C      125        1000
Ravi Kumar  475        5000

And this is my table 2

Name          Amount
-----        --------
Ravi c         1000
Ravi Kumar     5000

I want join these two tables based on name and bill amount.

This is my query

select b.PatientName,b.FinalAmt,MRNO,BILLNO from lekhi a LEFT join tpa b ON a.Name LIKE CONCAT('%', b.PatientName, '%') OR a.AMT=b.FinalAmt

Please help me out to get expected result

Name       Bill No    Amount
----      ----------  --------
Ravi C      125        1000
Ravi Kumar  475        5000

Upvotes: 0

Views: 140

Answers (2)

Mathew
Mathew

Reputation: 296

SELECT t1.`Name`,t1.`Bill No`,t1.`Amount`,t1.`Amount`
FROM table1 t1
JOIN table2 t2 ON (t1.`Amount`=t2.`Amount`) AND (UPPER(t1.`Name`)=UPPER(t2.`Name`));

Upvotes: 0

Shadow
Shadow

Reputation: 34304

If the 2 fields in both tables contain the same values:

select * from t1 left join t2 on t1.name=t2.name and t1.amount=t2.amount

If there are differences in the letter cases in the name fields:

select * from t1 left join t2 on lower(t1.name)=lower(t2.name) and t1.amount=t2.amount

If the above do not work for you, then you have to provide a lot more details about your source data and expected outcomes.

Upvotes: 1

Related Questions