I have a query that using a join and I want to change that query using a subquery not join. But using a subquery I'm getting a rows with a null values. How can I display a result without a null values? is there another way to enhance my query ?
This is my query with subquery.
select ri.id_room,ri.id_item,
(select r.room_name from tbl_rooms r where r.id = ri.id_room and r.id_property = 1) as 'Room Name',
(select r.sortvalue from tbl_rooms r where r.id = ri.id_room and r.id_property = 1) as 'Sort Value',
(select 'Room Status' = case when is_active = '1' then 'Active' else 'INACTIVE' end from tbl_rooms r where r.id = ri.id_room and r.id_property = 1),
(select i.itemname from tbl_items i where i.id = ri.id_item) as 'Item Code'
from tbl_roomitems ri
Output:
and this is my query using join.
select ri.id_room, r.room_name, r.sortvalue,'Status' = case when is_active = '1' then 'Active'
else 'INACTIVE' end from tbl_roomitems ri
left outer join tbl_rooms r on ri.id_room = r.id
where ri.id_item = 1 and r.id_property = 1
order by r.sortvalue, r.room_name
Output:
