SQL join query with appended columns

Question

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.

Have you tried inner join?

qxg
– qxg

2019-03-26 05:16:07 +00:00
Commented Mar 26, 2019 at 5:16 — qxg
– qxg, Commented Mar 26, 2019 at 5:16

Mureinik · Accepted Answer · 2019-03-26 05:15:08Z

1

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

answered Mar 26, 2019 at 5:15

Mureinik

316k54 gold badges402 silver badges406 bronze badges

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

2 Comments

Paul Wainwright Over a year ago

Mureinik, thanks for the reply. If I use the query I get 'invalid column name SubLocationName'. If I remove SubLocationName from the query it runs but returns 0 rows?

Janmejay Kumar Over a year ago

that means you do not have data in your table which match the records or by mistake you have selected different database. please verify once

Pavel Smirnov · Accepted Answer · 2019-03-26 08:12:04Z

0

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

edited Mar 26, 2019 at 8:12

Pavel Smirnov

4,8293 gold badges22 silver badges32 bronze badges

answered Mar 26, 2019 at 7:39

piraji kundgir

16

1 Comment

Paul Wainwright Over a year ago

Thanks piraji. Your query is exactly what I required. Paul.

Collectives™ on Stack Overflow

SQL join query with appended columns

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related