multiple resultsets: joins

Showing posts with label joins. Show all posts

Wednesday, March 28, 2012

Multi-table joins & tempdb growth / query performance

Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ' Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time'
Or does SQL do this automatically?
Thanks
Sean"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.com...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ' Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time'
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004sql

Multi-table joins & tempdb growth / query performance

Hi
When I run a LARGE query (20 tables, some have 4 million rows), it
takes hours to run and fills up 35GB on tempdb.
Could someone tell me, when joining these tables, does SQL server join
the entire table into tempdb or only the columns that are needed, i.e.
:
* returned in output
* used as part of the join
* used in a later join
I'm trying to reduce the time AND the impact on tempdb space, and I
think I can do this by doing the following:
Say (simple example) TableA joins to TableB which joins to TableC
I want to return the fields: A.1, B.1, C.1
I have to join A to B on A.2 = B.2
I have to join B to C on B.3 = C.2
Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
Does SQL merge (join) into tempdb the following after the first join:
A.1, B.1, B.3 ? Or does it merge & store the entire table?
If it does the entire table, I think I can speed things up & save time
by doing:
SELECT B.1, B.2, B.3
INTO TableB_temp
FROM TableB
Then join the query on field 2 & 3, but now tempdb can ONLY put a max
of 3 fields from TableB_Temp into storage during query processing...
saving space & hopefully (read/write) time?
Or does SQL do this automatically?
Thanks
Sean
"Sean" <plugwalsh@.yahoo.com> wrote in message
news:3698af3c.0405250724.523e4c7a@.posting.google.c om...
> Hi
> When I run a LARGE query (20 tables, some have 4 million rows), it
> takes hours to run and fills up 35GB on tempdb.
> Could someone tell me, when joining these tables, does SQL server join
> the entire table into tempdb or only the columns that are needed, i.e.
> :
> * returned in output
> * used as part of the join
> * used in a later join
> I'm trying to reduce the time AND the impact on tempdb space, and I
> think I can do this by doing the following:
> Say (simple example) TableA joins to TableB which joins to TableC
> I want to return the fields: A.1, B.1, C.1
> I have to join A to B on A.2 = B.2
> I have to join B to C on B.3 = C.2
> Table B has 10 columns (B.4, B.5, B.6, B.7, etc...)
> Does SQL merge (join) into tempdb the following after the first join:
> A.1, B.1, B.3 ? Or does it merge & store the entire table?
> If it does the entire table, I think I can speed things up & save time
> by doing:
> SELECT B.1, B.2, B.3
> INTO TableB_temp
> FROM TableB
> Then join the query on field 2 & 3, but now tempdb can ONLY put a max
> of 3 fields from TableB_Temp into storage during query processing...
> saving space & hopefully (read/write) time?
> Or does SQL do this automatically?
A merge join requires that the columns that it is joining them on are both
sorted. If it is using the tempdb then it is having to sort them into order
first, then merging them. It would speed things up if you indexed the
columns (the Primary Key will already be indexed, but not necessarily the
Foreign Key). As an index is already sorted, then the mergedb should not be
required, and the whole thing should run much faster
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.690 / Virus Database: 451 - Release Date: 22/05/2004

Multi-table joins & tempdb growth / query performance

Friday, March 9, 2012

multiple table joins, does the "ON" clause determine which two tab

How do you know which table is the intermediate or translation table in a
query using multiple joins.
For example:
SELECT T1.ID
FROM T1 LEFT OUTER JOIN T2 on T1.ID = T2.ID LEFT OUTER JOIN T3 ON T1.ID =
T3.ID
WHERE (T2.ID IS NULL) AND (T3.ID IS NULL)
T1
--
A
B
C
D
E
T2
--
A
X
B
Y
T3
--
B
H
R
J
K
X
E
Results of the query is:
C
D
In the above query is T2 the intermediate table or is it T1'
In both "ON" clauses T1 is used. This confuses me as to which table is the
intermediate.
Is there a way to use parenthesis to clarify the precedence or anything?
Thanks,
GeoffSELECT T1.ID
FROM T1
LEFT OUTER JOIN T2
on T1.ID = T2.ID
LEFT OUTER JOIN T3
ON T1.ID = T3.ID
WHERE (T2.ID IS NULL) AND (T3.ID IS NULL)
There is no "intermediate" table as I would think about that in an n:m
realtionsship. As it seems, the tabels all have the same primary keys which
sounds that they dont build up a many2many relationship. (Perhaps if you
would post up your real example and some DDL that would clarify the
situation a bit)

> Is there a way to use parenthesis to clarify the precedence or anything?
I always put tabs between the expression to chop up the important parts of
th query. (see above)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Geoff" <Geoff@.discussions.microsoft.com> schrieb im Newsbeitrag
news:3D111A57-3DB2-4202-A6BD-F1530880A665@.microsoft.com...
> How do you know which table is the intermediate or translation table in a
> query using multiple joins.
> For example:
> SELECT T1.ID
> FROM T1 LEFT OUTER JOIN T2 on T1.ID = T2.ID LEFT OUTER JOIN T3 ON T1.ID =
> T3.ID
> WHERE (T2.ID IS NULL) AND (T3.ID IS NULL)
> T1
> --
> A
> B
> C
> D
> E
> T2
> --
> A
> X
> B
> Y
> T3
> --
> B
> H
> R
> J
> K
> X
> E
>
> Results of the query is:
> C
> D
>
> In the above query is T2 the intermediate table or is it T1'
> In both "ON" clauses T1 is used. This confuses me as to which table is the
> intermediate.
> Is there a way to use parenthesis to clarify the precedence or anything?
> Thanks,
> Geoff
>

multiple table joins, does the "ON" clause determine which two

Thanks for the response.
Those are the "real" tables. I was doing an exercise in trying to figure out
as many ways as I could to do the "minus" operation on three tables.
I am trying to go to the next level of DB programming, that is move up from
basic SQL queries. I was reading about "translation" or "intermediate" table
s
when you do joins and I didn't really understand when such a table comes int
o
existance.
Here is what I was reading:
http://msdn.microsoft.com/library/d...r />
_0hwz.asp
"The middle table of the join (the titleauthor table) can be called the
translation table or intermediate table, because titleauthor is an
intermediate point of connection between the other tables involved in the
join.
When there is more than one join operator in the same statement, either to
join more than two tables or to join more than two pairs of columns, the joi
n
expressions can be connected with AND or with OR."
"Jens Sü?meyer" wrote:

> SELECT T1.ID
> FROM T1
> LEFT OUTER JOIN T2
> on T1.ID = T2.ID
> LEFT OUTER JOIN T3
> ON T1.ID = T3.ID
> WHERE (T2.ID IS NULL) AND (T3.ID IS NULL)
> There is no "intermediate" table as I would think about that in an n:m
> realtionsship. As it seems, the tabels all have the same primary keys whic
h
> sounds that they don′t build up a many2many relationship. (Perhaps if you
> would post up your real example and some DDL that would clarify the
> situation a bit)
>
> I always put tabs between the expression to chop up the important parts of
> th query. (see above)
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Geoff" <Geoff@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:3D111A57-3DB2-4202-A6BD-F1530880A665@.microsoft.com...
>
>But taht depends on your design wheter you have a 1-n or n-m relationship.
1-n One Customer can have multiple orders.
n-m One Author can write multiple book, but one book can be written by
multiple authors, therefore you need this intermediate table where you make
the connection between the values:
AUTHORS
========
AuthId AuthorName
1 Jens Smeyer
2 Peter Gedoens
BOOKS
======
BookId BookName
1 YourExampleBook
2 SQL Server the curse of relationals
3 Oracle sucks
AuthorBooks
=========
AuthorId BookId
1 1
2 1
1 2
1 3
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Geoff" <Geoff@.discussions.microsoft.com> schrieb im Newsbeitrag
news:EEA9BA2B-F086-4F4C-8A5B-C7788F033D9D@.microsoft.com...
> Thanks for the response.
> Those are the "real" tables. I was doing an exercise in trying to figure
> out
> as many ways as I could to do the "minus" operation on three tables.
> I am trying to go to the next level of DB programming, that is move up
> from
> basic SQL queries. I was reading about "translation" or "intermediate"
> tables
> when you do joins and I didn't really understand when such a table comes
> into
> existance.
> Here is what I was reading:
> http://msdn.microsoft.com/library/d.../>
09_0hwz.asp
> "The middle table of the join (the titleauthor table) can be called the
> translation table or intermediate table, because titleauthor is an
> intermediate point of connection between the other tables involved in the
> join.
> When there is more than one join operator in the same statement, either to
> join more than two tables or to join more than two pairs of columns, the
> join
> expressions can be connected with AND or with OR."
>
>
>
> "Jens Smeyer" wrote:
>

Multiple Table Joins Makes Query Go To Sleep

Hello All & Thanks in advance for your help!

Background:

1) tblT_Documents is the primary parent transaction table that has 10
fields and about 250,000 rows
2) There are 9 child tables with each having 3 fields each, their own
PK; the FK back to the parent table; and the unique data for that
table. There is a one to many relation between the parent and each of
the 9 child rows. Each child table has between 100,000 and 300,000
rows.
3) There are indexes on every field of the child tables (though I
don't believe that they are helping in this situation)
4) The client needs to be presented a view that has 5 of the main
fields from the parent table, along with any and all corresponding
data from the child tables.
5) The client will select this view by doing some pattern-matching
search on one of the child records' detail (e.g. field-name LIKE
%search-item% - so much for the indexes...)

Problem:

When I do the simple join of just the parent with one of the children,
the search works *fairly* well and returns the five parent fields and
the corresponding matching child field.

However, as soon as I add any one of the other child records to simply
display it's unique data along with the previously obtained results,
the resulting query hangs.

Is the overall structure of the tables not conducive to this kind of
query? Is this a situation where de-normalization will be required to
obtain the desired results? Or, more hopefully, am I just an idiot
and there is some simpler solution to this problem?!

Thanks again for your assistance!

- EdLets have the full table schema (including indexes and a few sample records)
of all your tables and a copy of the query(s) you are running.
http://www.aspfaq.com/etiquette.asp?id=5006

We'll take a look and see whats going on.

Mr Tea

<Ed_No_Spam_Please_Weber@.Weber_Spam_Not_Enterprises .Org> wrote in message
news:5jte111qne3gqh0k6o5etlif27jm1rseh1@.4ax.com...
> Hello All & Thanks in advance for your help!
> Background:
> 1) tblT_Documents is the primary parent transaction table that has 10
> fields and about 250,000 rows
> 2) There are 9 child tables with each having 3 fields each, their own
> PK; the FK back to the parent table; and the unique data for that
> table. There is a one to many relation between the parent and each of
> the 9 child rows. Each child table has between 100,000 and 300,000
> rows.
> 3) There are indexes on every field of the child tables (though I
> don't believe that they are helping in this situation)
> 4) The client needs to be presented a view that has 5 of the main
> fields from the parent table, along with any and all corresponding
> data from the child tables.
> 5) The client will select this view by doing some pattern-matching
> search on one of the child records' detail (e.g. field-name LIKE
> %search-item% - so much for the indexes...)
> Problem:
> When I do the simple join of just the parent with one of the children,
> the search works *fairly* well and returns the five parent fields and
> the corresponding matching child field.
> However, as soon as I add any one of the other child records to simply
> display it's unique data along with the previously obtained results,
> the resulting query hangs.
> Is the overall structure of the tables not conducive to this kind of
> query? Is this a situation where de-normalization will be required to
> obtain the desired results? Or, more hopefully, am I just an idiot
> and there is some simpler solution to this problem?!
> Thanks again for your assistance!
> - Ed|||(Ed_No_Spam_Please_Weber@.Weber_Spam_Not_Enterprise s.Org) writes:
> When I do the simple join of just the parent with one of the children,
> the search works *fairly* well and returns the five parent fields and
> the corresponding matching child field.
> However, as soon as I add any one of the other child records to simply
> display it's unique data along with the previously obtained results,
> the resulting query hangs.
> Is the overall structure of the tables not conducive to this kind of
> query? Is this a situation where de-normalization will be required to
> obtain the desired results? Or, more hopefully, am I just an idiot
> and there is some simpler solution to this problem?!

I can only echo what Lee said. Please include CREATE TABLE and CREATE INDEX
statements for the involved tables, and the query you are having problem
with.

My guess from your description is that you simply have an error in
you query...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hello All!

Per request, the following is the generate script for 4 tables; the
query that works 'OK' and the query that doesn't work.

<----- Table Defs Begin -------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Amounts_FK00]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblT_Amounts] DROP CONSTRAINT tblT_Amounts_FK00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_AssocBP_FK00]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblT_AssocBP] DROP CONSTRAINT tblT_AssocBP_FK00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_BlockBld_FK00]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblT_BlockBld] DROP CONSTRAINT tblT_BlockBld_FK00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Grantees_FK00]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblT_Grantees] DROP CONSTRAINT tblT_Grantees_FK00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Grantors_FK00]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblT_Grantors] DROP CONSTRAINT tblT_Grantors_FK00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_LotUnit_FK00]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblT_LotUnit] DROP CONSTRAINT tblT_LotUnit_FK00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Subdivision_FK00]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblT_Subdivision] DROP CONSTRAINT
tblT_Subdivision_FK00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_TaxIDs_FK00]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblT_TaxIDs] DROP CONSTRAINT tblT_TaxIDs_FK00
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Documents]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblT_Documents]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Grantees]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblT_Grantees]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Grantors]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblT_Grantors]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblT_Subdivision]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblT_Subdivision]
GO

CREATE TABLE [dbo].[tblT_Documents] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FileNo] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[DocType] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[YorDNo] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[DocDt] [datetime] NULL ,
[Book] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[Page] [int] NULL ,
[ARB] [nvarchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AudCreateWho] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AudCreateWhen] [datetime] NULL ,
[AudUpdateWho] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AudUpdateWhen] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblT_Grantees] (
[GranteeID] [int] IDENTITY (1, 1) NOT NULL ,
[MainID] [int] NULL ,
[GRANTEE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblT_Grantors] (
[GrantorID] [int] IDENTITY (1, 1) NOT NULL ,
[MainID] [int] NULL ,
[GRANTOR] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblT_Subdivision] (
[SubdivID] [int] IDENTITY (1, 1) NOT NULL ,
[MainID] [int] NULL ,
[Subdivision] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Documents] WITH NOCHECK ADD
CONSTRAINT [PK_tblT_Documents] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Grantees] WITH NOCHECK ADD
CONSTRAINT [PK_tblT_Grantees] PRIMARY KEY CLUSTERED
(
[GranteeID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Grantors] WITH NOCHECK ADD
CONSTRAINT [PK_tblT_Grantors] PRIMARY KEY CLUSTERED
(
[GrantorID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Documents] ADD
CONSTRAINT [DF__tblT_Docu__DocDt__1367E606] DEFAULT
(convert(datetime,convert(varchar,getdate(),1),1)) FOR [DocDt],
CONSTRAINT [DF__tblT_Docum__Page__145C0A3F] DEFAULT (0) FOR
[Page],
CONSTRAINT [DF__tblT_Docu__AudCr__15502E78] DEFAULT
(getdate()) FOR [AudCreateWhen],
CONSTRAINT [DF__tblT_Docu__AudUp__164452B1] DEFAULT
(getdate()) FOR [AudUpdateWhen],
CONSTRAINT [CK tblT_Documents DocDt] CHECK ([DocDt] >=
'1/1/1837' and [DocDt] <=
convert(datetime,convert(varchar,getdate(),1),1))
GO

CREATE INDEX [IX_FileNo] ON [dbo].[tblT_Documents]([FileNo]) ON
[PRIMARY]
GO

CREATE INDEX [IX_DocType] ON [dbo].[tblT_Documents]([DocType]) ON
[PRIMARY]
GO

CREATE INDEX [IX_DocDt] ON [dbo].[tblT_Documents]([DocDt]) ON
[PRIMARY]
GO

CREATE INDEX [IX_Book] ON [dbo].[tblT_Documents]([Book]) ON
[PRIMARY]
GO

CREATE INDEX [IX_Page] ON [dbo].[tblT_Documents]([Page]) ON
[PRIMARY]
GO

CREATE UNIQUE INDEX [ID] ON [dbo].[tblT_Documents]([ID]) ON
[PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Grantees] ADD
CONSTRAINT [DF__tblT_Gran__MainI__0EA330E9] DEFAULT (0) FOR
[MainID]
GO

CREATE INDEX [IX_MainID] ON [dbo].[tblT_Grantees]([MainID]) ON
[PRIMARY]
GO

CREATE INDEX [IX_Grantee] ON [dbo].[tblT_Grantees]([GRANTEE]) ON
[PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Grantors] ADD
CONSTRAINT [DF__tblT_Gran__MainI__09DE7BCC] DEFAULT (0) FOR
[MainID]
GO

CREATE INDEX [IX_MainID] ON [dbo].[tblT_Grantors]([MainID]) ON
[PRIMARY]
GO

CREATE INDEX [IX_Grantor] ON [dbo].[tblT_Grantors]([GRANTOR]) ON
[PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Subdivision] ADD
CONSTRAINT [DF__tblT_Subd__MainI__7B905C75] DEFAULT (0) FOR
[MainID],
CONSTRAINT [aaaaatblT_Subdivision_PK] PRIMARY KEY
NONCLUSTERED
(
[SubdivID]
) ON [PRIMARY]
GO

CREATE INDEX [{C3638ECB-1FC5-4012-BE3A-855E252E07B2}] ON
[dbo].[tblT_Subdivision]([MainID]) ON [PRIMARY]
GO

CREATE INDEX [GrantorID] ON [dbo].[tblT_Subdivision]([SubdivID]) ON
[PRIMARY]
GO

CREATE INDEX [MainID] ON [dbo].[tblT_Subdivision]([MainID]) ON
[PRIMARY]
GO

CREATE INDEX [Subdivision] ON
[dbo].[tblT_Subdivision]([Subdivision]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblT_Documents] ADD
CONSTRAINT [tblT_Documents_FK00] FOREIGN KEY
(
[DocType]
) REFERENCES [dbo].[tblM_DocTypes] (
[DocType]
)
GO

ALTER TABLE [dbo].[tblT_Grantees] ADD
CONSTRAINT [tblT_Grantees_FK00] FOREIGN KEY
(
[MainID]
) REFERENCES [dbo].[tblT_Documents] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[tblT_Grantors] ADD
CONSTRAINT [tblT_Grantors_FK00] FOREIGN KEY
(
[MainID]
) REFERENCES [dbo].[tblT_Documents] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

ALTER TABLE [dbo].[tblT_Subdivision] ADD
CONSTRAINT [tblT_Subdivision_FK00] FOREIGN KEY
(
[MainID]
) REFERENCES [dbo].[tblT_Documents] (
[ID]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

<----- Table Defs End -------
<----- Good Query Begins -------
SELECT DISTINCT d.ID, d.DocType, d.DocDt, d.Book, d.Page, gro.GRANTOR
FROM tblT_Documents d LEFT OUTER JOIN
tblT_Grantors gro ON d.ID = gro.MainID
WHERE (gro.GRANTOR LIKE N'%pracht%')

<----- Good Query Ends -------
<----- Failing Query Begins -------
SELECT DISTINCT d.ID, d.DocType, d.DocDt, d.Book, d.Page, gro.GRANTOR,
gre.GRANTEE, s.Subdivision
FROM tblT_Documents d LEFT OUTER JOIN
tblT_Subdivision s ON d.ID = s.MainID LEFT OUTER
JOIN
tblT_Grantees gre ON d.ID = gre.MainID LEFT
OUTER JOIN
tblT_Grantors gro ON d.ID = gro.MainID
WHERE (gro.GRANTOR LIKE N'%pracht%')

<----- Failing Query Ends -------|||Hello All!