469,625 Members | 1,086 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

sql querry : Union, Exists, IN

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
0 4522

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Eric Kincl | last post: by
2 posts views Thread by David Rasmussen | last post: by
4 posts views Thread by NickName | last post: by
4 posts views Thread by Elroyskimms | last post: by
4 posts views Thread by Akinia | last post: by
3 posts views Thread by Linn K B | last post: by
1 post views Thread by Jamie Pittman via AccessMonster.com | last post: by
6 posts views Thread by virus | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.