Fairly easy SQL query

[wapoc]

Hi.

I've got a chat system set up and it uses an ignore list. Now, rather than using two awful queries to find the ignored people by that user, and then find only the messages not written by them, can you suggest a query for the following pseudocode?

Select from chat where (no row in ignorelist where (field1 is the current username and field2 is the username of the chat message author))

The current username is a known constant. The chat message table has an author field. The ignorelist has two fields: the creator of the ignore request and the person to be ignored.

I have tried various things but either they were very slow or produced millions of rows (using distinct made it slow) or just didn't work at all.

Thanks!

Rob Pridham
wapocalypse.com

 

[randysmid]

Hi,
I presume that you are using this inside of a form. Have you considered using the DLookup function to quickly find out if this combination exists? Of course, your ignore list needs to be a table, so that it can be searched quickly. If the DLookup function returns nothing, then those two users are allowed to communicate with each other. So, instead of trying to do this in a query, do this directly inside your form. HTH,
Randy Smith
California Teachers Association

 

[wapoc]

It's for a WAP (mobile phone internet) site which although it uses forms, is very different to the HTML equivalents. I suppose though in a similar way I could use the "get from ignorelist" query, and then get all the messages and use PHP to process which ones are for output after. I'm just trying to reduce the load on the MySQL server.

 

[randysmid]

PHP? MySQL?
Is this the correct forum for your question? This is an Access queries forum.
HTH,
Randy Smith
California Teachers Association