By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,671 Members | 1,280 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,671 IT Pros & Developers. It's quick & easy.

sql querry : Union, Exists, IN

P: n/a
I have a querry that works, but it is incredeble slow, so do anyone
have any suggestions how to make this better:

The table looks like this
Gab(ID (key), Gid, Pid, Status, .....), mening there can be serveral
occurences of Gid in the table.

What i want is:

Everyone with staus F2
UNION
Everyone with status F1 or F, but has no occurrence of F2 (grouped on
Gid)
UNION
Everyone with status H, but has no occurrence of F2, F1, F (grouped on
Gid)
My suggestion, that is way to slow:
(SELECT G1.*
FROM Gab AS G1
WHERE G1.Status="F2")

UNION

(SELECT G2.*
FROM Gab as G2
WHERE (G2.Status="F" OR G2.Status = "F1") and NOT EXISTS
(SELECT GID
FROM Gab
WHERE G2.Status="F2" and Gab.Gid = G2.Gid))

UNION

(SELECT G3.*
FROM Gab as G3
WHERE G3.Status="H" and NOT EXISTS
(SELECT GID
FROM Gab
WHERE (G3.Status="F2" OR G3.Status="F" OR G3.Status="F1") and
Gab.Gid = G3.Gid))

so having:

Gab(ID (key), Gid, Pid, Status, .....)
(1, 1, 100, F2, ...)
(2, 1, 200, H, ...)
(3, 2, 300, F1, ...)
(5, 2, 500, F, ...)
(4, 3, 800, H, ...)

I should get the result:

(1, 1, 100, F2, ...)
(3, 2, 300, F1, ...)
(5, 2, 500, F, ...)
(4, 3, 800, H, ...)
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.