CODEWITHSUNDEEP
sql-server
S. Arzoo
S. Arzoo

Reputation: 73

Select value from different tables based on the column value in SQL Server

I have a main table A with the following fields:

enter image description here

Then I have three separate tables, each for Buildings, Classrooms and Offices. All these tables have two columns; ID and Name.

I want to query the table A to get the following result:

enter image description here

How can I do this?

Upvotes: 0

Views: 2626

Answers (2)

Bas Pauw
Bas Pauw

Reputation: 262

Should be doable with the use of a join.

Something along those lines should work:

SELECT tabelA.ID, tabelA.Subject, tabelA.Date, tabelA.locationType, tabelB.location
FROM tabelA INNER JOIN tabelB on tabelA.locationID = tabelB.locationID

Upvotes: 0

xQbert
xQbert

Reputation: 35323

Your data isn't really normalized. Having three separate tables all serving the same lookup is causing you some headache... so I unioned the 3 tables together and created a 'src' column so you could join table A's type and Id back to table B's ID and src. You'd have been better off having one table and non-repeating IDs and a type ID to specify if it's a building classroom or office.

Select * 
from A
LEFT JOIN (SELECT 'Building' as src, ID, Name FROM Buildings UNION ALL
           SELECT 'Classroom' as src, ID, Name FROM Classrooms UNION ALL
           SELECT 'Office' as src, ID, Name FROM Offices) B
on A.Location_Type = B.Src
and A.LocationID = B.ID

I used a left join here in case not all records in A have an associated record in B. However, an inner join should work as well.

Upvotes: 1

Related Questions