CODEWITHSUNDEEP
mysqlsql
mfrancisp
mfrancisp

Reputation: 119

INNER JOIN Using IF logic

How do I construct my left join to produce this output?

Table 1: Main Table

 |  ID  |  Team  |   Color    |
 |   1  |    A   |    Red     |  
 |   2  |    A   |    Blue    |  
 |   3  |    B   |    Green   |
 |   4  |    B   |    Green   |    
 |   5  |    C   |    Yellow  |

Table 2: Lookup Table

 |  Team  | Color     |  Final     |
 |    A   |  Red      |  A-1       |  
 |    A   |  Blue     |  A         |  
 |    B   |  Green    |  B         |  
 |    C   |  Yellow   |  C         |

Output Table

 |  ID  |  Team  |   Color    |  Final  |
 |   1  |    A   |    Red     |  A-1    |
 |   2  |    A   |    Blue    |  A      |
 |   3  |    B   |    Green   |  B      |
 |   4  |    B   |    Green   |  B      |  
 |   5  |    C   |    Yellow  |  C      |

In excel the logic would be something like this ... 

=IF(AND(Team = "A", Color = "Red"),"A-1", Team = Team))

How does this translate to an sql query in access or mysql using inner join? I have something like this but doesn't know how to construct an if statement within the query.

I want the query to check if Team = 'A' AND Color = 'Red' then return 'A-1' otherwise return the same Team column value.

SELECT Table1.ID, Table1.Team, Table1.Color, Table2.Final FROM Table1 
INNER JOIN Table2 ON Table1.Team = Table2.Team;

Any help woul dbe highly appreciated. Thanks a lot.

Upvotes: 1

Views: 123

Answers (4)

Flying Turtle
Flying Turtle

Reputation: 364

Try this 

SELECT A.ID, A.Team, B.Color, B.Final
FROM MainTable as A
LEFT JOIN LookupTable as B
    ON A.team = B.team
    AND B.Final = CASE COLOR WHEN "RED" THEN "A-1" ELSE B.Final END

Upvotes: 0

Edrich
Edrich

Reputation: 242

I have tried this and got your expected output.

SELECT 
    t1.id,
    t1.team,
    t1.color,
    t2.final
FROM
    table1 AS t1 LEFT JOIN table2 AS t2
    ON t1.Team = t2.Team AND t1.Color = t2.Color

Upvotes: 0

Alex Weinstein
Alex Weinstein

Reputation: 9891

You can use the AND keyword in your JOIN clause:

SELECT * FROM Table1  
INNER JOIN Table2 
ON Table1.Team = Table2.Team AND Table1.Color = Table2.Color

Upvotes: 2

Jens
Jens

Reputation: 69470

Try this untested query:

SELECT Table1.ID, Table1.Team, Table1.Color, 
case Table1.Team when "A" then
case Table1.Color when "READ" then
Table2.Final 
else 
Table1.Team
end
else
Table1.Team 
end
Table2.Final FROM Table1 
FROM Table1 
INNER JOIN Table2 ON Table1.Team = Table2.Team;

Upvotes: 0

Related Questions