CODEWITHSUNDEEP
phpmysqlsqlsearch
HCkev
HCkev

Reputation: 123

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:

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!

Upvotes: 1

Views: 413

Answers (2)

chaos
chaos

Reputation: 124335

SELECT DISTINCT c.*
FROM clients c
LEFT JOIN categories_assign a ON c.id = a.clientId
WHERE a.id IS NULL
OR a.categoryId IN (1, 7, 43, 52)

The a.id IS NULL gets those with no category assignments. The a.categoryId IN (...) gets those assigned to those categories.

If you're writing a query solely to get the uncategorized records, you don't need the DISTINCT clause. That's just there to eliminate duplicate records arising from the possibility that a client may be assigned to multiple categories you're looking for.

Upvotes: 1

Jodaka
Jodaka

Reputation: 1247

SELECT * FROM clients c 
WHERE c.id NOT IN (SELECT DISTINCT a.clientID FROM categories_assign a)

This will return all clients who do not appear in the categories_assign table. If you want to also do a search for clients with categories at the same time, just UNION this query and your query.

Upvotes: 0

Related Questions