How to join two tables with two conditions?

Question

I have two tables:

LLOAN

LOANID SOURCEID LOAN_COMPANY ETC
1      1        3
2      1        3
3      1        1
4      2        1
5      2        1
6      2        1
7      3        1
8      3        1

COMPANY

CompanyID CountryID CompanyIDLLAS 
1         1         1
2         1         2
3         1         3
4         2         1
5         3         1
6         4         1

And I want to join them. The SourceID refers to the CountryID and the LOAN_COMPANY refers to the CompanyID. Only country '1' has multiple companies, all the others just have one.

How can I join these two tables correctly? I've tried many different things, of which this came the closest:

SELECT Count(c.CompanyID) FROM dbo.LLOAN As l
LEFT JOIN dbo.Company As c ON c.CountryID = l.SourceID AND c.CompanyID = l.LOAN_COMPANY

But it leaves many rows blank. What is the correct way to join two tables with two conditions?

if you want exact match from both tables then remove LEFT from the query just JOIN — Tushar, Sep 16 '15 at 09:44
How did you get two similar columns in Company Table ? *CompanyID* i mean — The Reason, Sep 16 '15 at 09:44
You will need to learn about about `JOIN`... this is a great article: http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins — laylarenee, Sep 16 '15 at 09:45
@The, I'm sorry, the last colum has another name; it's now right in the OP — GijsApeldoorn, Sep 16 '15 at 09:52

score 1 · Accepted Answer · answered Sep 16 '15 at 09:42

1

Try below Query:

SELECT Count(c.CompanyID) 
FROM dbo.LLOAN As LL
LEFT JOIN dbo.Company As C 
ON (C.CountryID = LL.SourceID) 
AND (C.CompanyID = LL.LOAN_COMPANY)

answered Sep 16 '15 at 09:42

Aman Aggarwal

17,619
9
53
81

Thanks, that works for the most part, but there is one more problem: Some – GijsApeldoorn Sep 16 '15 at 09:55
@BasDeBeer:- What is the problem? – Rahul Tripathi Sep 16 '15 at 09:56

score 0 · Answer 2 · answered Sep 16 '15 at 09:41

0

You can group the condition using paranthesis like this:

SELECT Count(c.CompanyID) 
FROM dbo.LLOAN As l
LEFT JOIN dbo.Company As c ON (c.CountryID = l.SourceID) AND (c.CompanyID = l.LOAN_COMPANY)

answered Sep 16 '15 at 09:41

Rahul Tripathi

168,305
31
280
331

How to join two tables with two conditions?

2 Answers2