CODEWITHSUNDEEP
sqlsql-servert-sqlinner-join
user1540467
user1540467

Reputation: 31

INNER JOIN if null columns

x table 
id - name   - balance
1 - test    - 500
2 - test 2  - 400
3 - test 3  - 300

y table
idx - idy - name
1   - null
2   - 5
3   - null
4   - 6

SELECT x.Name, SUM(x.Balance)
FROM x
INNER JOIN y ON ???

if IS NULL y.idy ON (x.id = y.idx)
ELSE ON (x.id = y.idy)

My English is not enough to tell the problem. but my problem is understandable

Upvotes: 2

Views: 3637

Answers (4)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You could also try ANSI SQL standard coalesce() function to check null values

SELECT x.Name, SUM(x.Balance) [Balance] FROM x
join y ON coalesce(y.idy, y.idx) = x.id
group by x.Name

Result :

Name    Balance
test    500
test 3  300

Upvotes: 1

Robert Kock
Robert Kock

Reputation: 6018

Try this:

SELECT     x.Name,
           SUM(x.Balance)
FROM       x
INNER JOIN y
  ON       (y.idy IS NULL AND x.id = y.idx) OR (x.id = y.idy)
GROUP BY   x.Name;

Upvotes: 0

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

SQL HERE

You can use ISNULL like below :

SELECT x.Name, SUM(x.Balance)
FROM x
INNER JOIN y ON x.id = isnull(y.idy, y.idx)
group by x.Name

Upvotes: 1

Pred
Pred

Reputation: 9042

Boolean logic looks like this:

ON (y.idy IS NULL AND x.id = y.idx) OR (x.id = y.idy)

Upvotes: 0

Related Questions