Hi, If you've got a very complicated SELECT statement and some records aren't included because of a join, what is the easiest way to debug this and find the reasons why?
A:
I don't know if this will help you, but when I find myself with a complex Select that I'm having a hard time maintaining, or debugging, I'll break it up into separate common table expressions (CTE's). I've found this makes many of my queries much easier to understand and maintain.
Randy Minder
2010-02-02 13:58:29
A:
You could include your ON logic in the WHERE clause, phrase it like this:
WHERE 1=1
AND...
AND...
and just comment out as many of the terms until you isolate the unexpected behaviour.
davek
2010-02-02 14:00:39
+2
A:
Change the JOINS / INNER JOINS to OUTER JOINS and look for NULLs where they shouldn't be.
Mark Byers
2010-02-02 14:00:48
A:
If you have 10 joins, comment out the last 5.
Still have the problem? comment out the last 2/3 joins that are still uncommented
Still have the problem? comment out the last 1/2 joins that are still uncommented
Do this until you get down to it working, then the problem will lie in the last joins you commented out.
Yes you could do them one at a time but this is usually quicker.
Obviously you will have to comment out all the columns not used in the select statement, but I normally just put /* */ around all the columns, then put a * instead.
Just look at the number of results returned.
adolf garlic
2010-02-02 15:36:42