CODEWITHSUNDEEP
mysqljoin
JoCoaker
JoCoaker

Reputation: 643

MySQL select with INNER JOIN and CASE WHEN in INNER JOIN

I have a data base with Characters. Since Names of the Characters are not only use once the Names are seperate from the Characters. Since there are Male and Female characters I keep these in diffent Tables:

tbl_player, tbl_maleName, tbl_femaleName

In my MySQL Select query i'd like to Select the correct tables with an INNER JOIN depending on what gender they have. So something like this: (The SQL query is wrong and doesn't work :( )

 SELECT tbl_player.id, 
(CASE tbl_player.gender
    WHEN 'm' THEN tbl_maleName.name
    WHEN 'f' THEN tbl_femaleName.name
END) as gamerName
, tbl_player.gender
FROM tbl_player
INNER JOIN
(CASE tbl_player.gender
    WHEN 'm' THEN tbl_maleName ON tbl_player.gamerName = tbl_maleName.id
    WHEN 'f' THEN tbl_femaleName ON tbl_player.gamerName = tbl_femaleName.id
 END)

Thanks in advance!

Upvotes: 3

Views: 8856

Answers (2)

Roddy P. Carbonell
Roddy P. Carbonell

Reputation: 865

You need to use Union and create a value tableOption, with a default value:

SELECT tbl_player.id FROM tbl_player LEFT JOIN 
(
(SELECT 'male' as tableOption, name FROM tbl_maleName)
UNION
(SELECT 'female' as tableOption, name FROM tbl_femaleName)
) as gamerName 

ON gamerName.this = tbl_player.that
WHERE gamerName.tableOption = 'female'

If the value is female, then the values of the second table will be chosen.

Upvotes: -1

Paul Maxwell
Paul Maxwell

Reputation: 35583

Why you have chosen to use 2 tables for names I'm not certain, but I would not recommend it.

However, try this:

SELECT
      tbl_player.id
    , (CASE tbl_player.gender WHEN 'm' THEN tbl_maleName.name WHEN 'f' THEN tbl_femaleName.name END) AS gamerName
    , tbl_player.gender
FROM tbl_player
      LEFT JOIN tbl_maleName ON tbl_player.gamerName = tbl_maleName.id
                  AND tbl_player.gender = 'm'
      LEFT JOIN tbl_femaleName ON tbl_player.gamerName = tbl_femaleName.id
                  AND tbl_player.gender = 'f'

Upvotes: 4

Related Questions