Land Record.sql

SELECT
	l.value, -- Unique Identifier of Land
	l.name, -- Name of the Land
	l.description,
	l.location,
	(SELECT c.name FROM city c WHERE c.city_id = l.city_id) AS city,
	l.longitude,
	l.latitude,
	l.dimentions,
	(SELECT u.name FROM c_uom u WHERE u.c_uom_id = l.c_uom_id) AS uom,
	l.historicalcost,
	l.purchasecost,
	(SELECT s.name FROM status s WHERE s.status_id = l.status_id) AS current_status,
	(SELECT EXTRACT(YEAR FROM l.acquisitionyear)) AS acquisition_year,
	
	(SELECT
			mp.market_price
		FROM
			market_price mp
		WHERE
			mp.land_id = l.land_id
		AND
			mp.isactive = 'Y'
		
	) AS Market_Price
	
	
FROM 
	Land l
WHERE
	l.isactive = 'Y'