After fixing your view, and looking a bit closer at the data in this sample code, your problem may also be in the how you are joining the data together.

If...

--check whether the source has some duplicate value..

SELECT ID, COUNT(st.ID)

FROM

sourcetable st

GROUP BY. stID

HAVING COUNT(st.ID) > 1

--check whether there is issue with the join clause somehow to retrieve dup data

SELECT...

I am not sure what you are trying to achieve .But above OR is redudant..

select * from T1

inner join T2

on T1.Col1 = '1'

It will give cross join..with all rows with value 1 for col1 will cross join with table t2..

Why are you not using this condition in the select statement which is inserting...

I'm not going to say your wrong. But I will say that I think the problem has been twisted and trounced that it's not even the same problem anymore....

Select...

HOLY Recursive Statement Batman!!

I just noticed that the Joins don't have the NOLOCK hint, but the views all DO have the no lock hint.... so by switching to the View,...

these two...

Then for the record he is having issue has isdeleted as 1..

with view it will return row..but with table.. just no row...

Simple thing is that when he is using...

When the query was access the tables directly, they needed to select only the records where ISDELETED was NULL or 0, correct? Therefor the isnull(ISDELETED,0) = 0 for each table...

Okay, lost. The view will not have any records where ISDELETED = 1. This query is having all table references changed to use views built over the tables. These views...

drop table a

go

drop table b

go

drop view myvwab

go

create table a ( id int)

go

create table b (id int,isdeleted int null)

go

insert into a select 1

go

insert into a select 2

go

insert into b select 1,1

go

create...

suppose one of your record has isdeleted = 1

when table will be joined... suppose joining column is presnet in mani table as well in this table. Then isdeleted will be...

This is the problem in where clause

AND ISNULL(CHILD.ISDELETED,0) = 0

Also why to use left join when you are using the where clause....

What abt the main query where it is showing diff results..