sql querry : Union, Exists, IN
Linn K B
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, ...)
James Fortune
> I have a querry that works, but it is incredeble slow, so do anyone
> have any suggestions how to make this better:
> 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, ...)
What a nasty SQL puzzle. It could take days
to figure that one out. I'm not a magician :-).
Create tblOutput with the same structure as Gab
except make ID just a long, then try this:
'---------------------------------
Private Sub cmdNastySQL_Click()
Dim MyDB As Database
Dim GidRS As Recordset
Dim GroupRS As Recordset
Dim CloneRS As Recordset
Dim OutputRS As Recordset
Dim strSQL As String
Dim lngI As Long
Dim lngCount As Long
Dim boolOutput As Boolean
Dim boolF As Boolean
Dim boolF1 As Boolean
Dim boolF2 As Boolean
Dim boolH As Boolean
Dim boolCheckDone As Boolean
Set MyDB = CurrentDb
strSQL = "SELECT DISTINCT Gid FROM Gab;"
Set GidRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
'Note: make sure tblOutput has the same structure as Gab.
'Make tblOutput.ID just a Long to get the same output order you have
strSQL = "SELECT * FROM tblOutput;"
Set OutputRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
If GidRS.RecordCount > 0 Then
GidRS.MoveLast
lngCount = GidRS.RecordCount
GidRS.MoveFirst
For lngI = 1 To lngCount
boolOutput = False
boolF = False
boolF1 = False
boolF2 = False
boolH = False
boolCheckDone = False
'Get the group
strSQL = "SELECT * FROM Gab WHERE [Gid] = " & GidRS("Gid") & ";"
Set GroupRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
GroupRS.MoveFirst
Do While Not GroupRS.EOF
If GroupRS("Status") = "F2" Then
'This record will always be output
boolOutput = True
Else
'Need to see if it will be output
'The boolean values will be true for the entire group
'so don't do the calculation over and over
boolOutput = False
If Not boolCheckDone Then
Set CloneRS = GroupRS.Clone
CloneRS.MoveFirst
CloneRS.FindFirst "[Status] = 'F'"
If Not CloneRS.NoMatch Then boolF = True
CloneRS.MoveFirst
CloneRS.FindFirst "[Status] = 'F1'"
If Not CloneRS.NoMatch Then boolF1 = True
CloneRS.MoveFirst
CloneRS.FindFirst "[Status] = 'F2'"
If Not CloneRS.NoMatch Then boolF2 = True
CloneRS.MoveFirst
CloneRS.FindFirst "[Status] = 'H'"
If Not CloneRS.NoMatch Then boolH = True
CloneRS.Close
Set CloneRS = Nothing
boolCheckDone = True
End If
'Now see if the record is to be output
If (boolF1 Or boolF) And Not boolF2 Then boolOutput = True
If boolH And Not boolF2 And Not boolF1 And Not boolF Then boolOutput = True
End If
If boolOutput = True Then
OutputRS.AddNew
OutputRS("ID") = GroupRS("ID")
OutputRS("Gid") = GroupRS("Gid")
OutputRS("Pid") = GroupRS("Pid")
OutputRS("Status") = GroupRS("Status")
OutputRS.Update
End If
GroupRS.MoveNext
Loop
GroupRS.Close
Set GroupRS = Nothing
If lngI <> lngCount Then GidRS.MoveNext
Next lngI
End If
GidRS.Close
Set GidRS = Nothing
OutputRS.Close
Set OutputRS = Nothing
Set MyDB = Nothing
MsgBox ("Done.")
End Sub
'---------------------------------
I'd be interested in whether or not this runs faster than what you have.
James A. Fortune
MacDermott
Above all, make sure you have an index on fields such as Status and ID.
I'm told that UNION queries themselves run pretty quickly, so that's
probably not the problem.
Subqueries like yours tend to run slowly, because the subquery must be
recalculated for each record.
Here's an alternative approach to the second group in your UNION query -
also untested:
Create a query like this:
SELECT DISTINCT ID FROM GAB WHERE Status="F2"
Name that query qNoF2
You can then create a second query like this:
SELECT DISTINCT GAB.* FROM GAB RIGHT JOIN qNoF2 ON GAB.ID=qNoF2 WHERE
qNoF2.ID IS NULL AND GAB.STATUS IN ("F1","F2")
My guess is that this will run faster than what you have,
but I too am curious to hear how it goes.
- Turtle
James Fortune
> I'm no magician, either, but here are a few insights I might share:
>
> Above all, make sure you have an index on fields such as Status and ID.
Thanks, Turtle. Sometimes things that are obvious to me aren't that
obvious to others. For the VBA implementation Gid should definitely be
indexed also. Indexing Gid probably has an effect on the speed of
the original SQL string because of the part that matches Gid's.
James A. Fortune