Wednesday, December 19, 2007

SQL, Joins, Unions and more .. continued

Hi again.

Today I made a test - transferred the data into MS SQL Server 2005 Express SP 2.0.

Guess What ? :)

The situation is almost the same ! Wondering why the result was messed, the execution time was arround '274709' - I guess this is the "ms" - "duration" column in the MS SQL Server Profiler. Which is arround 5 minutes.
The mess I am talking about is the following:
The situation is that in of the tables I have about 120k records, which should be displayed, on the other one I have about 5k records. Insted of showing "120000" for "di_count", and "5000" for "ei_count", in the result I got '594163480' (which is the number of rows in one of the left joined tables multiplied by the number of rows in the other one) for each of them - the same figure for the two columns. What is that ?

I just want to make a select from one table, than join second table, then join third table to the FIRST one, then join the last table AGAIN to THE FIRST one. Why is it joining the result alltogether?

Anyway .. using UNION, VIEW and work with the VIEW solves the problem, but I was just wondering why this happens ...

I j ust can't help showing you these two screens:

For the MSSQL Profiler:


And for the MySQL (note: the execution is still not finished!):


I have no comment!

No comments: