CODEWITHSUNDEEP
sql-server
Paul Wainwright
Paul Wainwright

Reputation: 15

SQL join query with appended columns

I have the below SQL Server tables:

Asset Table 

+---------+-----------+------------+
| AssetID | AssetName | LocationID |
+---------+-----------+------------+

Location Table 

+-------------+---------------+------------------+
|  LocationID |  LocationName |  SubLocationName |
+-------------+---------------+------------------+

How can I return results where the Assets Table LocationID = Location Table LocationID but also append the corresponding LocationName and SubLocationName as extra columns?

Assets Table 

+---------+-----------+------------+
| AssetID | AssetName | LocationID |
+---------+-----------+------------+
|       1 | Asset1    |        123 |
+---------+-----------+------------+

Location Table 

+------------+--------------------+----------------------+
| LocationID | LocationName       | SubLocationName      |
+------------+--------------------+----------------------+
|        123 | Area1              | Sub1                 |
+------------+--------------------+----------------------+

would return:

+--------+-------+------+
| Asset1 | Area1 | Sub1 |
+--------+-------+------+

Thanks Paul.

Upvotes: 1

Views: 67

Answers (2)

piraji kundgir
piraji kundgir

Reputation: 16

    SELECT a.AssetID,a.AssetName,l.LocationName, l.SubLocationName
    FROM   Assets a
    JOIN   Location l ON a.LocationId = l.LocationId

Upvotes: 0

Mureinik
Mureinik

Reputation: 311573

You need to join the tables and query the columns you're interested in:

SELECT AssetName, LocationName, SubLocationName
FROM   Assets a
JOIN   Location l ON a.LocationId = l.LocationId

Upvotes: 1

Related Questions