SQL Server – Conditional Query And Multiple Tables Join (Many to Many Query)

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.

The database is available here, I have used a sample DB for one of the SQL exams of SoftUni. Pretty much, after you run the sql to create a DB, you should get the following diagram:diagramFootball

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:

eurozoneExample

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:

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:

international

 

 

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:

Enjoy it! 🙂

About

VBA Developer

Tagged with: , , , ,