CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2008-r2
Gil Peretz
Gil Peretz

Reputation: 2419

different ways to perform this query?

I'm struggling to find efficient way to perform the following problem.

I have 3 tables:

1) Events tbl that keeps the following data:

enter image description here

2) Tasks tbl that keeps the following data:

enter image description here

The logic is that:

I want a query that will show the list of events that need to have one of the following rules:

either the event is a phase OR the event have a task(dont have to be phase)

My current query is :

SELECT DISTINCT * FROM 
(
    SELECT Event_ID FROM
    (
        SELECT 
            E.Event_ID 
        FROM
            Events E 
        WHERE E.Event_IsPhase = 1
    ) ET UNION
    (
        SELECT 
            T.RelatedGateID
        FROM
            Tasks T
    )
)AS tbl1 LEFT JOIN
Tasks AS tbl2 ON tbl1.Event_ID=tbl2.RelatedGateID

*The last LEFT JOIN is for additional data that I need have.

My question is : I would like to know if there is different way to do this query (more efficiently) ? (right now It has some performence time issues)

Upvotes: 1

Views: 65

Answers (5)

Alex
Alex

Reputation: 8937

Maybe this one:

SELECT Event_ID FROM 
(
    SELECT  Event_ID,
    (select top 1 T.RelatedGateID from Tasks T where T.RelatedGateID=l1.Event_ID) as par 
    FROM Events l1 
    WHERE l1.Event_IsPhase=1
) l2 
WHERE l2.par IS NOT NULL

Upvotes: 0

user359040
user359040

Reputation:

Your existing query won't return your desired dataset - instead, it will return all task records.

Instead, try:

select e.Event_ID, t.*
from Events e
left join Tasks t ON e.Event_ID=t.RelatedGateID
where e.Event_IsPhase = 1 or t.RelatedGateID is not null

Upvotes: 1

Meherzad
Meherzad

Reputation: 8553

try this 

select distinct E.Event_ID from Tasks T, Events E 
WHERE (E.Event_ID = T.RelatedGateID) or 
(E.Event_IsPhase = 1)

Hope this helps

Upvotes: 1

Rajneesh
Rajneesh

Reputation: 2291

How about 

SELECT DISTINCT E.Event_ID  FROM Events E left outer join Tasks AS tbl2 ON tbl1.Event_ID=tbl2.RelatedGateID WHERE E.Event_IsPhase = 1 OR COUNT(tasks.taskID) > 0 GROUP BY (E.Event_ID)

Upvotes: 0

cjk
cjk

Reputation: 46465

How about:

SELECT DISTINCT Event_ID
FROM Events e
LEFT JOIN Tasks t on e.Event_ID = t.RelatedGateID
WHERE e.Event_IsPhase = 1 OR t.RelatedGateID IS NOT NULL

Upvotes: 1

Related Questions