Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers 3

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.

share|improve this answer
add comment

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
;
share|improve this answer
add comment

You can reduce the time of your query only by indexing the columns that you are using. id and otherval in your case.

But be very careful before you enable an index on a column as this can even affect you adversely.

Thanks Arun

share|improve this answer
    
The question asks if there is a better way to write the query not how can I alter my DB to improve performance. –  James Anderson 19 hours ago
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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