Selecting two values from the same table but under different conditions

Question

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?

Answer 1

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.

Answer 2

You could use grouping and conditional aggregating, like this:

SELECT
  id,
  MAX(CASE OtherVal WHEN 100 THEN MyVal END) AS MyVal1,
  MAX(CASE OtherVal WHEN 200 THEN MyVal END) AS MyVal2
FROM MyTable
WHERE OtherVal IN (100, 200)
GROUP BY
  id
;