Checking data from long query

Transact-SQL
1

Hi,

I'm pretty newbies for SQL Querying, would like to know, when we have a very long SQL query with multiple tables, and then we have some data missing which is expected to be shown but in returned is not, how to check this ? I mean how to break this very long query but all inter-connected each other.

I have a very long and complex query, by using SQL Formatter, I would have almost 900 lines (if I copied the query to Excel, just to see it more clear). It is contain many tables join each other so I'm wondering how to check all this when I have some problem with the data. Like currently I have missing data, which in the main table (the primary table) it is exists, but with this very long query with join to many tables, the data is not coming out.

And what is the technique or the activity of looking at this kind of problem ? cause I want to take a look whether there is a specific courses about this only. If I search courses online, about SQL or data analyst, typically I get the full course, from beginning like how to create table, how to join, sub querying, filtering, aggregates, stuff like that, which I know it a bit or so. What I don't know is how to handle an issue when there is no data come out or the result is wrong from a very complex query. How to get to the exact place/position within the long query, which is the main problem that my data is not come out or wrong.

Any advice is much appreciated. Also about what and where to look for such courses maybe in Udemy or any such online training.

Thanks in advance.

2

There's no course or analysis tool I'm aware of that addresses "why isn't my data showing up?". Generally the way to handle this is to simplify the query starting with a single table, or just a few JOINs, that give you the data you're expecting. Then JOIN one more table and ensure that you're still seeing the data you expect.

At some point you'll JOIN a new table and get the wrong/incomplete results, you then backtrack and see why the new JOIN is excluding data. Typically this happens if you're doing an INNER JOIN and the new table does not have any rows that satisfy the JOIN condition. INNER JOINs will only return data that relates in both tables, so changing that JOIN to a LEFT OUTER JOIN might correct your results.

If you can, feel free to post your query here, someone might be able to spot something.

4 Likes