Hi msimoo,
You have to write the query like below.
DECLARE @ads AS TABLE(AdsID INT,UID INT,Section VARCHAR(50),Category VARCHAR(50),Country VARCHAR(50),State VARCHAR(50)
,City VARCHAR(50),AdsTit VARCHAR(50),AdsDesc VARCHAR(50),AdsPrice VARCHAR(50),Img1 VARCHAR(50),Currency VARCHAR(50)
,AdsDate DATETIME,approvAds CHAR(1))
INSERT INTO @ads VALUES(1,1,'Section1','Category1','India','MaharAShtra','Mumbai','AdsTit1','AdsDesc1','5000','Img1','Rs.',GETDATE(),'Y')
INSERT INTO @ads VALUES(2,2,'Section2','Category2','India','MaharAShtra','Mumbai','AdsTit2','AdsDesc2','2000','Img2','Rs.',GETDATE(),'N')
INSERT INTO @ads VALUES(3,NULL,'Section3','Category3','India','MaharAShtra','Mumbai','AdsTit3','AdsDesc3','3000','Img3','Rs.',GETDATE(),'Y')
DECLARE @UserInfo AS TABLE(UID INT,approv CHAR(1))
INSERT INTO @UserInfo VALUES(1,'Y')
INSERT INTO @UserInfo VALUES(2,'N')
INSERT INTO @UserInfo VALUES(3,'N')
INSERT INTO @UserInfo VALUES(4,'Y')
SELECT AD.[AdsID]
,AD.[UID]
,AD.[Section]
,AD.[Category]
,AD.[Country]
,AD.[State]
,AD.[City]
,SUBSTRING([AdsTit],1,30)+'...' AS AdsTit
,SUBSTRING([AdsDesc],1,85) AS AdsDesc
,AD.[AdsPrice]
,AD.[Img1]
,AD.[Currency]
,AD.[AdsDate]
,AD.[approvAds]
,UI.[approv]
FROM @ads AS AD
JOIN @UserInfo AS UI ON AD.[UID] = UI.[UID]
where AD.[Country] = 'India' AND AD.[approvAds]= 'Y' AND UI.[approv]='Y'
UNION
SELECT DISTINCT AD.[AdsID]
,AD.[UID]
,AD.[Section]
,AD.[Category]
,AD.[Country]
,AD.[State]
,AD.[City]
,SUBSTRING([AdsTit],1,30)+'...' AS AdsTit
,SUBSTRING([AdsDesc],1,85) AS AdsDesc
,AD.[AdsPrice]
,AD.[Img1]
,AD.[Currency]
,AD.[AdsDate]
,AD.[approvAds]
,UI.[approv]
FROM @ads AS AD,@UserInfo AS UI
WHERE AD.[UID] IS NULL AND AD.[Country] = 'India' AND AD.[approvAds]= 'Y' AND UI.[approv]='Y'
ORDER BY AD.[AdsDate] DESC