Hendrik Breezy Hendrik Breezy - 2 months ago 8
SQL Question

MS SQL Query to find Name by joining over several tables

Project Table

Project
ShortDescription
Description
DB_ID
ProjektKMPoolStartDatum
ProjektKMPoolEndeDatum


ProjectManager

DB_ID
ProjektID
KontaktID


Contact

Benutzeraccount
EMail


Imagine the following scenario:
in ProjectManager the owner of the project is listed (
KontaktID
is referring to Contact,
ProjektID
is referring to Project)

I need the query to find all Project.Project(so the real name not the ID) for KontaktID=somevalue

For some of you this might be easy, but I'm really awful in SQL so any help would be awesome :)

Sample Data:

ProjectManager

DB_ID ProjektID KontaktID
43 14 25


Project

Project ShortDescription Description DB_ID ProjektKMPoolStartDatum ProjektKMPoolEndeDatum
ABC-Test Fantasie-Projekt !!! Diese Projekt enhält nur Fantasie-Namen !!! 14 2015-02-27 07:34:42.000 NULL


Contact

DB_ID Name EMail
37 Ram Bo xxx

Answer

SQL 101

select p.Project, c.Name as ContactName
from Contact c
inner join ProjectManager m  on (c.DB_ID = m.KontaktID)
inner join Project p on (m.ProjektID = p.DB_ID)
where c.DB_ID = @KontaktID;

You can replace @KontaktID by a hardcoded value, or declare the variable and set it before running the sql.

If you just need a list of the projects for a KontaktID then the Contact table doesn't even need to be in the SQL

select p.Project
from ProjectManager m
inner join Project p on (m.ProjektID = p.DB_ID)
where m.KontaktID = @KontaktID;