SQL Server - Inner Join On - Conditional Statement

Question

I am using SQL Server 2008 and have the following scenario:

I have Table a with fields id and groupId.

I have Table b with fields id and groupId also.

The rule in Table b, is that:

If a.id = 0, then a.groupId = b.groupId
Else a.id = b.id (in which case a.groupId = 0)

The 2 tables are also linked by agrId such that a.agrId = b.agrId

How can I join these tables, whilst satisfying the rule above?

Update: Apologies for the lack of clarity, I have updated the rule and added my attempt below:

select * from a
inner join b
on a.agrId = b.agrId
where (
    (b.id > '0' and b.groupId = '0') 
    or
    (b.groupId > '0' and b.id = '0')
  )

You can join "conditional" using CASE etc, but if it is high performance you're after, specialized queries are better. — Allan S. Hansen, Jun 12 '14 at 06:04
Join with what? ID = 0, join with GroupID, GroupID = 0, join with ID? — Jesuraja, Jun 12 '14 at 06:11
Keep in mind that using `OR` in `JOIN`s can hurt performance really bad. You'd better write them as equi-joins and do a `UNION`. Have a look over here: http://stackoverflow.com/questions/5901791/is-having-an-or-in-an-inner-join-condition-a-bad-idea — NickyvV, Jun 12 '14 at 07:33

user2321864 · Answer 1 · 2014-06-12T06:22:19.843

2

Try this

SELECT *
FROM TableA A
INNER JOIN TableB B
    ON (B.ID = 0 AND A.groupId = B.groupId)
        OR (B.groupId = 0 AND A.id = B.id)

edited Jun 12 '14 at 06:22

answered Jun 12 '14 at 06:06

user2321864

2,207
5
25
35

can u please edit anything in your answer .. i misunderstood the answer and downvoted but now its since more then 5 min i cannot rollback it – Dhaval Jun 12 '14 at 06:16

gjvdkamp · Answer 2 · 2014-06-12T06:06:38.373

0

Al it needs is an expression that evaluates to true or false, to indicate if these two rows should be joined. So based on what you say, this would be the join condition:

select  *
from    tA a 
        inner join tB b 
            on (a.id = 0 and a.groupid = b.groupid) 
            or (a.groupid = 0 and a.id = b.id)

edited Jun 12 '14 at 06:06

answered Jun 12 '14 at 06:01

gjvdkamp

9,929
3
38
46

Jesuraja · Answer 3 · 2014-06-12T06:38:38.777

0

Try this:

SELECT *
FROM   a INNER JOIN
       b ON a.agrId = b.agrId
WHERE  (b.id = '0' AND b.groupId = a.groupId) 
       or
       (b.groupId = '0' AND b.id = b.id)

edited Jun 12 '14 at 06:38

answered Jun 12 '14 at 06:04

Jesuraja

3,774
4
24
48

SQL Server - Inner Join On - Conditional Statement

3 Answers3