7

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?

9

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

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
;

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