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

    @wolfram77, that is still a join.

  • 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
    ;
    

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM