Joining Two tables from two different database but same server

SELECT WEBDATA.dbo.Members_Initial.MemberStatus, tempdb.dbo.[#temp1].*
FROM tempdb.dbo.[#temp1] LEFT OUTER JOIN
WEBDATA.dbo.Members_Initial ON tempdb.dbo.[#temp1].MASTER_CUSTOMER_ID = WEBDATA.dbo.Members_Initial.PID
WHERE (WEBDATA.dbo.Members_Initial.DT_STATUS = 1)

I am joining two tables. one is #temp1 and other is Members_Initial. #temp1 table having 427 rows but i am getting 92 only. i am left outer join only.

When you specify a column from the right table in your where clause, that effectively results in an inner join. Move the logic in your where clause to the JOIN condition like this:

SELECT  WEBDATA.dbo.Members_Initial.MemberStatus ,
        tempdb.dbo.[#temp1].*
FROM    tempdb.dbo.[#temp1]
        LEFT OUTER JOIN WEBDATA.dbo.Members_Initial ON 
			tempdb.dbo.[#temp1].MASTER_CUSTOMER_ID = WEBDATA.dbo.Members_Initial.PID
			AND ( WEBDATA.dbo.Members_Initial.DT_STATUS = 1 );
1 Like

Thanks a lot.