MySQL Select entry with no associated entry(in another table)
===

I am making an custom online management application for a company where users can manage clients, products, components, providers, etc. Right now I am working on a search form, where users can search for clients using keywords and various options. Now, for the keywords search, I know how to use MySQL FullText, the problem is more related to the extra search options.


So basically, every client can be assigned to one or more categories. A client might also no be assigned to any category. To do so, I use three tables:

- "clients" table, which holds the basic information of the clients, such as name, contact info, etc. Each clients has a unique ID
- "categories" table, which simply has ID, title and description fields
- "categories_assign" table, which has only two fields, "clientId" and "categoryId". It allows me to assign multiple category to a client.


Now, in the search form, the user is able to select categories to search in; multiple categories can be selected at once, as well as a "None" value, which should search clients without category. A client without any category will basically not have any entry in the "categories_assign" table.

That's my problem: I have no problem searching for clients assigned to specific categories, but I can't find a way to search clients Without category..

Here's a simplified version (for readability purposes) of the query that select clients according to the selected categories:

    SELECT * FROM clients c, categories_assign a WHERE c.id = a.clientId AND a.categoryId IN(1,7,43,52)


So as you might expect, this select clients which is assigned to categories ID 1, 7, 43 or 52. This does works fine. However, as I stated before, I can't find a way to select clients without categories, ie. clients that do not have any entry in the "categories_assign" table. I want to be able to select specific categories too, at the same time. (for example, search for categories ID 1, 7, 43, 52 AND clients without category). I tried using joins, but without luck.

The only workaround I can think of is to create an entry in the "categories_assign" table with "categoryId" set to 0, meaning "no category", so i'd simply need to search for "categoryId" 0. I'd like to avoid the need of doing this, if possible.

Do anyone has any clues?

Thanks in advance!