Im not so good at SQL, so I asked you guys for help on writing a query.
SQL Query - Table Joining Problems
I got an answer and it works! Its just noticeably slow. I hate to do it but Im really hoping someone out there wants to recommend some ways to optimize the query. I have not even attempted this on my own because I dont know enough about SQL to even begin googling.
What might help is to create indexes on the columns you're joining with. For example;
CREATE INDEX name_for_index ON Main (StatusID);
It generates a look-up table for this column that the algoritm that performs the query will use.
Edit: If you're not allowed to change the database, you may be out of luck. I have seen cases where easing on the JOIN statements improved the performance, that would be this;
... FROM Main m, Status st, Secondary s WHERE st.ID = m.StatusID AND s.MainID = m.ID AND ( s.MainID IS NULL AND m.WhenDate = <YourDate> OR s.MainID IS NOT NULL AND s.WhenDate = <YourDate> ) AND TypeId = <TypeFilter> AND ... other filters, if you need any ...
And then handling your other case, where the
INNER JOIN is needed a bit more verbose.