SQL Query of two table joined not retuning the desired results

I have a query I am trying to return Parent and the child.
There are two tables I am dealing with.
One that has all documents of both Parent and Child and the other table with all child documents with a reference to the Parent.

Table 1
Select d.DocId,DocumentKey   From Documents as D Where DocId = '7363'

Open in new window

Table 2
Select dr.Docid,Dr.AbsoluteParentId From DocumentRelations as DR Where AbsoluteParentId = '7363'

Open in new window

DocId       DocumentKey
----------- ----------------------------------------------------------------------------------------------------
7363        CON0000545

(1 row(s) affected)

Docid       AbsoluteParentId
----------- ----------------
7364        7363
7365        7363

My goal is to have it generate a list with all Docid and AbsoluteParentID  whether they are NULL or not.

I tried using a Left outer join as well as a Cartesian and both just return the two Child results.
LVL 27
yo_beeDirector of Information TechnologyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark ElySenior Coldfusion DeveloperCommented:
Select D.DocumentKey ,   Dr.AbsoluteParentId 
From DocumentRelations as DR 
	INNER JOIN Documents as D 
		ON DR.AbsoluteParentId = D.DocId
Where DR.AbsoluteParentId = '7363'

Open in new window

yo_beeDirector of Information TechnologyAuthor Commented:
Still only returning 2 records when I am expect 3 (1 Parent and 2 Children).
Mark ElySenior Coldfusion DeveloperCommented:
Select D.DocID, DR.DocID , DR.AbsoluteParentID, ISNULL(D.DocumentKey,'No Key') AS DocumentKey
From Documents as D
	FULL OUTER JOIN DocumentRelations as DR 
		ON D.DocID = DR.DocId
	LEFT OUTER JOIN DocumentRelations AS D2
		ON DR.AbsoluteParentId = D2.docID

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

yo_beeDirector of Information TechnologyAuthor Commented:
Thank you.
yo_beeDirector of Information TechnologyAuthor Commented:
I did a slight mode to your recommendation by removing the Left Join For the second table of DocumentRelations and created a Cartesian For Documents and DocumentFields_0010 then did a full Outer and this got me my desired results.

Select D.DocID, DR.DocID , DR.AbsoluteParentID,d.documentkey,Coalesce(DF.Field000047,D.field000047) 
From (Select td.DocId,td.DocumentKey,tdf.Field000047 from Documents td inner join DocumentFields_0010 TDF on td.DocId = TDF.DocId ) as D
	FULL OUTER JOIN DocumentRelations as DR 
		ON D.DocID = DR.DocId
		Left outer join DocumentFields_0010 df on dr.AbsoluteParentId = df.DocId
		order by d.DocumentKey

Open in new window

Mark ElySenior Coldfusion DeveloperCommented:
Thanks for the update Yo Bee!  Good luck with your project.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.