CODEWITHSUNDEEP
sql-serverjoin
IrfanClemson
IrfanClemson

Reputation: 1779

sql server select from two tables as null or value

This shouldn't be hard but somehow I am unable to make this work. So I have two tables: Splanning_Buildings and Splanning_RoomData. The primary key of OBJECTID in the Splanning_Buildings table is represented as Objectid_bldg in the Splanning_RoomData table.

What I need is to select all rows from the Splanning_Buildings table in such a way that if its value is found inside the Splanning_RoomData table then show the value as "Existing" string; but if not found in Splanning_RoomData then show as blank.

This is SQL Server 2012.

Here is my sql:

SELECT t1.objectid, name, t2.building_n  FROM splanning_buildings t1 LEFT JOIN splanning_roomdata t2 ON t1.objectid = t2.objectid_bldg WHERE t2.objectid_bldg IS NULL

SELECT t1.objectid, name, t2.building_n  FROM splanning_buildings t1 LEFT JOIN splanning_roomdata t2 ON t1.objectid = t2.objectid_bldg

The first query returns all rows from the splanning-buildings table except the row which exists the splanning_RoomData table; so t2.building_n is 'null'. Makes sense.

The second query returns all rows from the buildings table but multiple t2.buildings_n rows.

None of these are the desired results! I guess I need some kind of sql Case() function?

Any idea? Thanks!

Upvotes: 1

Views: 61

Answers (2)

Stepan Novikov
Stepan Novikov

Reputation: 1396

  SELECT t1.objectid, name,
        CASE WHEN (EXISTS(SELECT TOP(1) 1 FROM splanning_roomdata t2 WHERE     t1.objectid = t2.objectid_bldg )) THEN 
        'Exists'
        ELSE 'Not exists' END AS [status]
    FROM splanning_buildings t1

Upvotes: 1

Carsten Massmann
Carsten Massmann

Reputation: 28196

select objectid, 
  (select top 1 1 from room_data 
   where objectid_bldg=objectid) 
  as roomdata_exists from buildings

I simplified your table names a little bit - hope you can still make sense of it. ;-)

Upvotes: 0

Related Questions