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
  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?

2 Answers 2


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,
  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.

  • 1
    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).
    – wolfram77
    Oct 31, 2017 at 8:42
  • 2
    @wolfram77, that is still a join.
    – datagod
    Nov 21, 2018 at 2:55

You could use grouping and conditional aggregating, like this:

  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)

Not the answer you're looking for? Browse other questions tagged or ask your own question.