Selecting a filtered group result

[daanoz]

hey

I'm trying to make a query which selects all rows that are not linked to the a certain number, i already managed to do this with a subquery, but this slows down the application, mainly caused because i have more then 100k rows.

(simplefied) table
############################
# RowId # MessageId # Type #
############################
# 1     # 104       # 3    #
# 2     # 104       # 3    #
# 3     # 104       # 4    #
# 4     # 104       # 5    #
# 5     # 104       # 4    #
# 6     # 104       # 8    #
# 7     # 105       # 3    #
# 8     # 105       # 2    #
# 9     # 105       # 1    #
# 10    # 106       # 4    #
# 11    # 106       # 3    #
# 12    # 107       # 4    #
# 13    # 107       # 6    #
# 14    # 107       # 5    #
# 15    # 107       # 9    #
############################
 

so, a Message consists out of several types, now i want to select the messageId's that do not contain the type: 8,9 or 1. which is in this case message 106

is there any way to do this without a subquery?

Greetz Daanoz

[Delerna]

Just a suggestion as I don't know the full details of your situation. It may or may not be possible for you

When I find that doing subqueries on large tables is too slow then I look to see
if I can bring a reduced number of records from the table into a table variable

I then perform the suquerying operation on the table variable instead.

Also you may have already done this so again, just suggesting, but do you have good indexes on the table.
Regards

[daanoz]

Just a suggestion as I don't know the full details of your situation. It may or may not be possible for you

When I find that doing subqueries on large tables is too slow then I look to see
if I can bring a reduced number of records from the table into a table variable

I then perform the suquerying operation on the table variable instead.

Also you may have already done this so again, just suggesting, but do you have good indexes on the table.
Regards

Sadly i'm not allowed to make any changes to the database, it's read-only because it's used by a bunch of other applications (not my choice ;))

[daanoz]

ok... solved by letting my PHP script set-up subresults, so the sub query doesn't have to be repeated time after time, but just once

Greetz Daanoz

[ck9663]

ok... solved by letting my PHP script set-up subresults, so the sub query doesn't have to be repeated time after time, but just once

Greetz Daanoz

the catch: you have to make sure your resultset is updated by the time it's accessed

-- CK