December 15, 2011 at 10:15 am
For me, nested joins can be a bit difficult to read/follow so I was trying to convert the following (original formatting included) -- Thanks QotD (missed it too)
FROM PO_Header RIGHT JOIN (Job INNER JOIN ((PO_Detail RIGHT JOIN Source ON PO_Detail.PO_Detail = Source.PO_Detail)
INNER JOIN Job_Operation ON Source.Job_Operation = Job_Operation.Job_Operation) ON Job.Job = Job_Operation.Job)
ON PO_Header.PO = PO_Detail.PO
where PO_Header.PO_Type=0 and PO_Detail.PO_Type=0 AND Source.PO_Type=0
This is what I have managed to come up with...
from Job j
inner join Job_Operation jo on j.Job = jo.Job
left outer join Source s on jo.Job_Operation = s.Job_Operation
left outer join PO_Detail pod on s.PO_Detail = pod.PO_Detail
inner join PO_Header poh on pod.PO = poh.PO
In my instance, they return the same results. However, I still may have a logic error.
TIA
Cheers
December 16, 2011 at 6:51 am
First of all, have a look at your execution plan, it can give you a few hints. If you do, you'll notice that the execution plan only has inner joins, no outer joins at all. This is due to your last inner join (PO_Header). An inner join require that there are matching information on both sides, eg the left side has to be not null. Thus the previous outer join makes no sense to the query optimizer, and it is smart enough to change it to an inner join. I suspect that you want the last join to be an left outer join too.
December 16, 2011 at 3:06 pm
I will give that a go again...
Thanks
December 18, 2011 at 2:32 pm
The database & schema are not mine per se -- It is from a commercial ERP system that we own licenses to and it primarily uses Crystal Reports (no SSRS) for it's reporting.
I am rewriting some of the reports as SSRS reports or straight SQL extracts for ease of use by the folks in accounting and to automate delivery to inboxes. I do my rewrites and in this instance summarization and compare to the reports to make sure I'm not missing something. For this instance I needed to try to tear down what they did to locate keys/relations that are missing from their documentation. I can post what little I have when in the office.
Thanks
December 18, 2011 at 3:39 pm
Don't mind Joe. He likes offending and insulting people.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2011 at 4:45 am
Roger that.
Thanks 🙂
December 19, 2011 at 11:33 am
Where condition was missing in my rewrite in the original post...
from Job j with(nolock)
inner join Job_Operation jo with(nolock) on j.Job = jo.Job
left outer join Source s with(nolock) on jo.Job_Operation = s.Job_Operation
left outer join PO_Detail pod with(nolock) on s.PO_Detail = pod.PO_Detail
inner join PO_Header poh with(nolock) on pod.PO = poh.PO
where poh.PO_Type = 0 and pod.PO_Type = 0 and s.PO_Type = 0
Execution plan appears to be the same for both. All the joins in the execution plan are inner.
However, if I remove the where condition from both, the plans relative to each other are no longer the same.
December 19, 2011 at 12:18 pm
Looks like this is it....
from Job j with(nolock)
inner join Job_Operation jo with(nolock) on j.Job = jo.Job
inner join [Source] s with(nolock) on jo.Job_Operation = s.Job_Operation
left outer join PO_Detail pod with(nolock) on pod.PO_Detail = s.PO_Detail
left outer join PO_Header poh with(nolock) on poh.PO = pod.PO
where poh.PO_Type = 0 and pod.PO_Type = 0 and s.PO_Type = 0
With and without the conditional, relative to each other, they are the same.
Thanks
December 19, 2011 at 12:23 pm
Err... by the way http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 19, 2011 at 12:25 pm
Word of caution. You have two left outer joins, then you have where clause predicates that will exclude null values, essentially turning those joins back to inner joins. Is that what you want? Do you want the rows that don't have matches to po_detail (and hence get nulls for all columns) to be excluded because PO_Type is NULL not 0? Or should those rows come through?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 7:18 am
Yep. I noted that they all turned into inner joins in the execution plan. To get the execution plans to match both with and without the where clause, both left outer joins were needed. However, logically it should break down as such:
Jobs will have Job_Operations.
Job_Operations consist of tasks performed internally (listed within the Job_Operations table) and/or tasks that are outsourced (listed in Job_Operations but further details are elsewhere). There is no "documented" link between Job_Operation & Source. Their documentation only consists of key/foreign key relations. Finding this relation took some digging. The "links" are indexed.
Job_Operation
FK_Job_on_JobOp Job Job Job
That is the only foreign key relation relevant for these 5 tables.
Tasks that are outsourced should have a purchase order for them. Not everything in Source has an associated PO_Detail.
Purchase Orders should consist of PO_Header and PO_Detail. If something was purchased (a PO_Detail) there should be both a detail and corresponding header record.
The where clause eliminates quotes -- quotes to order and actual orders are in the same tables but are differentiated via the PO_Type flag.
The report is only pulling closed job information so they cannot be updated at this point. We had problems in the past (around the time I started working where I am currently) with people using Excel 2003 to query data directly against the SQL server. They would run large queries and lock significant portions of the database and eventually bring everything to a standstill. To mitigate this, they had implemented nolock queries and the caveats were explained. For my reports, it's not necessary, but if I create a view for others to make use of... I could probably use temp tables/table vars as an alternative
The piece I am working on is summarizing services provided by an outside entity so Source could and probably should be an inner join. For that matter, the rest can too.
Initially, my goal was to just rewrite the nested joins I found digging, hence the outer joins. However, explaining the hole process helped.
A bit long winded...
Thanks
Cheers
December 20, 2011 at 8:44 am
I don't think you answered my question
GilaMonster (12/19/2011)
Do you want the rows that don't have matches to po_detail (and hence get nulls for all columns) to be excluded because PO_Type is NULL not 0? Or should those rows come through?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2011 at 9:42 am
PO_Type is appears to be 0 for a regular order and 1 for a quote. [AFAIK It should be one or the other. However, the database design, does allow NULL so I will have to confirm with the vendor as to whether this is always populated or not.]
From what I can tell, for this report, I need what was ordered (no quotes) so yes, I need PO_Type = 0 across the board.
If I notice a case where I need the Source information but not any of the PO information, I would need to eliminate/refine the where clause.
Hopefully I did a better job explaining.
Thanks
December 20, 2011 at 10:34 am
m mcdonald (12/20/2011)
PO_Type is appears to be 0 for a regular order and 1 for a quote. [AFAIK It should be one or the other. However, the database design, does allow NULL so I will have to confirm with the vendor as to whether this is always populated or not.]
Ok, but you put a left join in. A left join means that if there is no match to the second table, all of its columns appear as NULL. So, if that's happened, if you have rows that don't have matches in the Po_details (and hence have null values), do you want them to show up or not?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply