I want to grab a value from a table into two different columns for different values from the same table. Use this query as an example (notice how the select is on the same table aliased as 2 different tables):
SELECT a.myVal, b.myVal
FROM MyTable a, MyTable b
WHERE
a.otherVal = 100 AND
b.otherVal = 200 AND
a.id = b.id
When I run a relatively simple query like this on my dataset, it works – it just takes a long time. Is there a better/smarter way of writing this query?
Best Answer
For readability I would rewrite the query using the more modern join syntax. This will clearly separate your join conditions from your filters.
For performance, ensure you have proper indexes. In this limited example, ideally you would have a clustered index on ID and a non-clustered index on OtherVal.
After looking at your query, however, I cannot tell just what it is you are trying to accomplish.