Selecting two values from the same table but under different conditions
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?
For readability I would rewrite the query using the more modern join syntax. This will clearly separate your join conditions from your filters.
select a.myVal, b.myVal from MyTable a join MyTable b on b.id = a.id where a.OtherVal = 100 and b.Otherval = 200
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.
to add to @datagod: `SELECT a.column, b.column FROM table AS a, table AS b WHERE a.column=x AND b.column=y` could be another way to avoid using `JOIN` (when not necessary).