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

Question

I have a main table A with the following fields:

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:

How can I do this?

How could we possibly know what you want from this? Where do the other values come from? Is this as simple as needing a join? blog.codinghorror.com/a-visual-explanation-of-sql-joins That's my guess but it is hard to say. If that doesn't do it then you need to look here. spaghettidba.com/2015/04/24/… — Sean Lange
– Sean Lange, Commented Mar 7, 2016 at 14:31
Have a look at JOIN and InnerJoin :D w3schools.com/sql/sql_join.asp — Felix D.
– Felix D., Commented Mar 7, 2016 at 14:31

xQbert · Accepted Answer · 2016-03-07 14:32:11Z

1

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.

answered Mar 7, 2016 at 14:32

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bas Pauw · Accepted Answer · 2016-03-07 14:32:57Z

0

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

answered Mar 7, 2016 at 14:32

Bas Pauw

2622 silver badges12 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related