As I have already started some time ago with SQL Server, in this article I will present the way to write a if-else case in sql query.
Conditional Query with SQL
The idea of the conditional query is to display some value in a column, depending on another value. While this task is basic in any programming language, in SQL it requires some research. Thus, let’s imagine that you want to get a table, telling you which country is in the Eurozone and which is not. We do not have a column for this, but we may use Countries.CurrencyCode column to tell us which currency a country is using. If the currency is in the Eurozone, we should write “inside”. This an example of the way it looks:
The way to achieve it is to use CASE selection in the SQL. We have to tell the server what to generate in specific case. Like this:
SELECT CountryName, CountryCode,
ORDER BY CountryName
So, pretty much SQL gives us a good possibility here.
Multiple Tables Join
The task for the second query is a little more difficult. We need to get a result like this:
There are three columns, each coming from a different table. This is quite OK, but this is not a trivial 1:1:1 example. If we take a second look, Arsenal, Bayer, and Bayern are playing both in the UEFA Champions League and their home league. Furthermore, the league country is international, which is not a value from our table. Thus, we need to bond the tables in a way that gives us the team as many times as it is present it all leagues. E.g.(Arsenal twice for two leagues, AS Roma once for one league). Thus, we need to use a forth table, which had enabled us to build the many-to-many query. The forth table is Leagues_Teams.
Last but not least, we should try to swap the null values with “international”, whenever we do not have a value for the League.
How to do this? Here comes the code:
t.TeamName [Team Name],
ISNULL(c.CountryName,'International') [League Country]
FROM Teams t
JOIN Leagues_Teams lt ON lt.TeamId = t.Id
JOIN Leagues l ON lt.LeagueId = l.Id
LEFT JOIN Countries c ON c.CountryCode = l.CountryCode
ORDER BY t.TeamName ASC
Enjoy it! 🙂