CODEWITHSUNDEEP
sqldatabase
user3630809
user3630809

Reputation: 33

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 I'm 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)

Upvotes: 1

Views: 1571

Answers (2)

WonderWorker
WonderWorker

Reputation: 9092

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.

Upvotes: 4

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

select *
from appmaster a
inner join appservmap asm on a.appid=asm.appid
inner join servicemaster s on asm.servid=s.servid

Upvotes: 0

Related Questions