user3630809 user3630809 - 9 days ago 5
SQL Question

SQL Multiple select from three tables with foreign keys

I need to select from table application(left) and table services(right) the name of the values in the middle table. since the middle table contains all foreign keys of the table application and services. i know im supposed to use join or something. HELP!

http://s1078.photobucket.com/user/Stephanie_Faith_Feliciano/media/ben_zps0a55e33c.png.html

Table definitions:

Table 1: Application

Column 1: int ApplicationID (PK)
Column 2: nvarchar(255) Name


Table 2: Service

Column 1: int ServiceID (PK)
Column 2: nvarchar(255) Name


Mapping table: ApplicationToService

Column 1: int ApplicationToServiceID (PK)
Column 2: int ApplicationID (FK)
Column 3: int ServiceID (FK)

Answer

You will indeed require a JOIN command.

In the following example I use the INNER JOIN command as it's the most commonly used (at least for me), but you may replace with another join if you prefer.

Here is a diagram of the different types of joins at your disposal: Print me out and hang me up

Based on the assumption that your tables are:

Table 1: Application

  • Column 1: int ApplicationID (PK)
  • Column 2: nvarchar(255) Name

Table 2: Service

  • Column 1: int ServiceID (PK)
  • Column 2: nvarchar(255) Name

Mapping table: ApplicationToService

  • Column 1: int ApplicationToServiceID (PK)
  • Column 2: int ApplicationID (FK)
  • Column 3: int ServiceID (FK)

Example:

SELECT
    *
FROM 
    ApplicationToService
    INNER JOIN Application ON ApplicationToService.ApplicationID = Application.ApplicationID
    INNER JOIN Service ON ApplicationToService.ServiceID = Service.ServiceID

You may replace the * with individual fields, but since you are now dealing with multiple tables, you must precede each column name with the table name like so:

SELECT
    ApplicationToService.ApplicationToServiceID
    , Application.Name
    , Service.Name
FROM 
    ApplicationToService
    INNER JOIN Application ON ApplicationToService.ApplicationID = Application.ApplicationID
    INNER JOIN Service ON ApplicationToService.ServiceID = Service.ServiceID

Your table and column names may be less verbose than mine, but I find it good practice to keep them descriptive.

If you need this explaining further then don't be afraid to ask.