Re: SQL-Query with many equal subselects

Originally posted by Steve
> I've searched the web for hours to find a solution for the following
> question, but nobody seems to have this problem except me ;-)
>
> I created a simple query that shows the problem. As you can see there
> are 2 main queries, both of them use an additional filter (SELECT id
> FROM T_profession WHERE id < 1000). It's quite unefficient to run this
> sub-query twice, so is it possible to place this query somewhere else
> and just run it once ? Making a view doesn't work as this subquery is
> being build at runtime within an application.
>
> Any help would be highly appreciated...
> Steve
>
>
> SELECT *
> FROM (SELECT 'Engineer', COUNT (*)
> FROM T_Profession
> WHERE description LIKE '%Engineer%'
> AND id IN (SELECT id FROM T_Profession WHERE id < 1000)),
> (SELECT 'Manager', COUNT (*)
> FROM T_Profession
> WHERE description LIKE '%Manager%'
> AND id IN (SELECT id FROM T_profession WHERE id < 1000))
>
> --------------------------------------------------------
>

I don't understand your problem either, since what you want to do can be simplified to:

SELECT *
  FROM (SELECT 'Engineer', COUNT (*)

          FROM T_Profession
         WHERE description LIKE '%Engineer%'
           AND id < 1000),
       (SELECT 'Manager', COUNT (*)
          FROM T_Profession
         WHERE description  LIKE '%Manager%'
           AND id < 1000);

Or you could get a vertical result like this:

SELECT CASE WHEN description LIKE '%Engineer%' THEN 'Engineer' ELSE 'Manager' END col1,

       COUNT(*)
FROM T_Profession
WHERE (description LIKE '%Engineer%' OR description LIKE '%Manager%') AND id < 1000
GROUP BY CASE WHEN description LIKE '%Engineer%' THEN 'Engineer' ELSE 'Manager' END;

--
Posted via http://dbforums.com