Min() query - 5th level optimization

I'm pulling multiple campaigns associated with companies so that I
have a result set with company name, campaign1, campaign2, campaign3,
campaign4, campaign5 where the campaigns are listed in alpha order (by
campaign_code) and each campaign column includes only the one campaign
(or null). When I got to the 5th level of MIN's, the query seemed to
expand way beyond what was required for the 4th level join and I
believe there's "bloat" in the expression. The query works as is, but
I was wondering if anyone has a suggestion for optimizing? I tried to
retain the tabulation when I pasted from TOAD, but had to do some
cleanup anyways so sorry if I didn't get it exactly.

Again, this works, but I think it could be better.

Not show are the joins for campaigns 1-4.

Basic tables are: oncd_company: company_id, company_name_1
oncd_company_campaign: company_campaign_id, company_id, campaign_code
onca_campaign: campaign_code, description, use_for_company

SELECT
oncd_company.company_id,
oncd_company.company_name_1,
campaign_desc5.description d5
FROM oncd_company
LEFT OUTER JOIN oncd_company_campaign campaign5 ON
campaign5.company_id = oncd_company.company_id
AND campaign5.campaign_code =
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <> /* Not like Min */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')
AND campaign_code <> /* Not like Min 2 */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'))
AND campaign_code <> /* Not like Min 3 */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')))
AND campaign_code <> /* Not like Min 4 Subset */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <> /* Not like Min */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')
AND campaign_code <> /* Not like Min 2 */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'))
AND campaign_code <> /* Not like Min 3 */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')
AND campaign_code <> (SELECT
MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')))
AND campaign_code <> /* Not like Min 4 */
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'))
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')))
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y'
AND campaign_code <>
(SELECT MIN(campaign_code)
FROM onca_campaign
WHERE use_for_company = 'Y')))))))
LEFT OUTER JOIN onca_campaign campaign_desc5 ON
campaign_desc5.campaign_code = campaign5.campaign_code
ORDER BY oncd_company.company_name_1

Re: Min() query - 5th level optimization

Chris H (chollstein@broadreachpartnersinc.com) writes:
> I'm pulling multiple campaigns associated with companies so that I
> have a result set with company name, campaign1, campaign2, campaign3,
> campaign4, campaign5 where the campaigns are listed in alpha order (by
> campaign_code) and each campaign column includes only the one campaign
> (or null). When I got to the 5th level of MIN's, the query seemed to
> expand way beyond what was required for the 4th level join and I
> believe there's "bloat" in the expression. The query works as is, but
> I was wondering if anyone has a suggestion for optimizing? I tried to
> retain the tabulation when I pasted from TOAD, but had to do some
> cleanup anyways so sorry if I didn't get it exactly.

Since I don't have your tables, I cannot write a query for them. But here
is an example for the Orders table in the Northwind database that lists
the first five orders for each customer, and in order.

WITH numbered (CustomerID, OrderID, rn) AS (
SELECT CustomerID, OrderID,
row_number() OVER(PARTITION BY CustomerID ORDER BY OrderID)
FROM Orders
)
SELECT CustomerID,
MIN(CASE rn WHEN 1 THEN OrderID END),
MIN(CASE rn WHEN 2 THEN OrderID END),
MIN(CASE rn WHEN 3 THEN OrderID END),
MIN(CASE rn WHEN 4 THEN OrderID END),
MIN(CASE rn WHEN 5 THEN OrderID END)
FROM numbered
GROUP BY CustomerID
ORDER BY CustomerID

This solution requires SQL 2005, using a Common Table Expression and
the row_number() function.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx