CODEWITHSUNDEEP
sqlsql-server
HappyFreddie
HappyFreddie

Reputation: 135

How to write "if else" in LEFT JOIN

I need to write logic in SQL Server like this:

SELECT A.x, B.y
FROM A
    LEFT JOIN B
        ON A.a = B.a AND 
            --the logic begins, I'll use Python.
            if B.b in A.b:
                return True
            elif B.c in A.b:
                return True
            elif B.d in A.b:
                return True
            else:
                return False

I used CASE WHEN but it seems wrong. I'll explain the logic. There's 3 keywords. If 1 is matched, don't test 2 or 3. If 1 is not, test if 2 is matched. If yes, don't test 3. If not, goes to 3. If 1 is matched, I don't need to match 2 and 3 anymore. But when I use CASE WHEN, 2 and 3 still match when 1 is already matched and gives me a lot of duplicates.

Can anyone help?

Sample data:

Table A and Table B

+-----------+----------------+    +-----------+----------------+------+------+
| publisher | y              |    | publisher | y              | a    | b    |
+-----------+----------------+    +-----------+----------------+------+------+
|  lion     |applebananapeach|    |  lion     |applebananapeach|apple |      |
|  dragon   |applepeach      |    |  dragon   |     peach      |apple |peach |
+-----------+----------------+    +-----------+----------------+------+------+

In row 1: When A.y = B.y then True. Don't match even if B.a in A.y. Cause in a left join, it will duplicate.

In row 2: WHEN A.y <> B.y then, if B.a in A.y then Ture. Don't match even if B.b in A.y. Cause it will duplicate.

This is my code if it can help you understand my question:

SELECT *
FROM gp
LEFT JOIN (
    SELECT DISTINCT
       map.[a],
       map.[b],
       map.[keyword1] ,
       map.[keyword2] ,
       map.[keyword3] ,
       map.[keyword4]
       FROM [dbo].[map]
    ) AS map
        ON  (1 = 
        CASE 
        WHEN gp.[a] = map.[a] AND gp.[b] = map.[b] THEN 1 
        WHEN gp.[a] = map.[a] and gp.[b] LIKE CONCAT('%',map.[keyword1], '%') THEN 1 
        WHEN gp.[a] = map.[a] and gp.[b] LIKE CONCAT('%',map.[keyword2], '%') THEN 1 
        WHEN gp.[a] = map.[a] and gp.[b] LIKE CONCAT('%',map.[keyword3], '%') THEN 1 
        WHEN gp.[a] = map.[a] and gp.[b] LIKE CONCAT('%',map.[keyword4], '%') THEN 1 
        ELSE 0
        END

Upvotes: 0

Views: 225

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270473

If I understand correctly, use three LEFT JOINs and COALESCE() to get the value you want.

SELECT A.x, COALESCE(Bb.y, Bc.y, Bd.y)
FROM A LEFT JOIN
     B Bb
     ON A.a = Bb.a AND 
        A.b = Bb.b LEFT JOIN
     B Bc
     ON A.a = Bc.a AND 
        A.b = Bc.b AND
        bb.a IS NULL LEFT JOIN
     B Bd
     ON A.a = Bd.a AND 
        A.b = Bd.b AND
        Bc.a IS NULL

Upvotes: 1

Related Questions