CODEWITHSUNDEEP
sqlsql-server
OM Asphyxiate
OM Asphyxiate

Reputation: 329

Where Exists optimization

I'm attempting to optimize my query a little, and my thought was to find the existing accounts I was looking for using the EXISTS clause. This is what I came up with:

CREATE VIEW recruit_rev AS
SELECT p.clinic, CONCAT(p.last_name, ', ', p.first_name) AS doctor, p.email, p.hire_date, p.practice_date, q.period_id, q.fiscal_year, SUM(t.amount) AS production
FROM transactions t
INNER JOIN provider p
ON (t.clinic=p.clinic AND p.provider_id=t.provider_id)
CROSS JOIN period q
WHERE (t.time_ran BETWEEN q.period_start AND q.period_end)
AND impacts='P'
AND EXISTS (
        SELECT p.provider_id
        FROM provider p
        WHERE (p.clinic=t.clinic AND p.provider_id=p.provider_id)
        AND p.position_id=1
        AND p.email LIKE '%-Time%')
GROUP BY p.clinic, p.email, p.hire_date, p.practice_date, q.period_id, q.fiscal_year, p.first_name, p.last_name

However this doesn't work, my current working query that I'm using just has the filters as part as the normal query and without the EXISTS.

AND p.position_id=1
AND p.email LIKE '%-Time%'

Any insight where I took the wrong turn? Thanks in advance.

Upvotes: 0

Views: 108

Answers (2)

JamieD77
JamieD77

Reputation: 13949

unless your database design in atypical, and provider_id in the provider table is not unique per row (provider), your query should probably just look like this.

SELECT  p.clinic,
        CONCAT(p.last_name,', ',p.first_name) AS doctor,
        p.email,
        p.hire_date,
        p.practice_date,
        q.period_id,
        q.fiscal_year,
        SUM(t.amount) AS production
FROM    transactions t
        INNER JOIN provider p ON p.provider_id = t.provider_id)
        INNER JOIN period q ON t.time_ran BETWEEN q.period_start AND q.period_end
WHERE   impacts = 'P'
        AND p.position_id = 1
        AND p.email LIKE '%-Time%'
GROUP BY p.clinic,
        p.email,
        p.hire_date,
        p.practice_date,
        q.period_id,
        q.fiscal_year,
        p.first_name,
        p.last_name

You would only use EXISTS if provider_id can be in provider table multiple times with different settings and you want to include the provider if any of the different settings match your filter

Upvotes: 0

Bruce Dunwiddie
Bruce Dunwiddie

Reputation: 2908

Don't use the same alias, "p" in this case, in both the inner subquery and the outer scope.

AND EXISTS (
        SELECT p.provider_id
        FROM provider p2
        WHERE (p2.clinic=t.clinic AND p2.provider_id=p.provider_id)
        AND p2.position_id=1
        AND p2.email LIKE '%-Time%')

Upvotes: 3

Related Questions