CODEWITHSUNDEEP
mysqlsql
Mbrouwer88
Mbrouwer88

Reputation: 2292

Mysql query select non-members

I have 2 tables:

Customers:
- ID
- NAME

Modulemembers:
- ID
- customer_id
- enabled

I use this to enable a function of my site for some customers. I have a form where the admin of the site can add the module for a customer, so i need a query that looks for customers that are NOT member of the modulemembers table.

I made this:

SELECT customers.id, 
       customers.name 
FROM   customers, 
       modulemembers 
WHERE  customers_id != modulemembers.cust_id 
ORDER  BY customers.name

but it does not work. What am I doing wrong?

Upvotes: 0

Views: 499

Answers (3)

Matt Busche
Matt Busche

Reputation: 14333

You can use a LEFT OUTER JOIN and filter based on NULLs. The query below will pull in all results from the customers table and the modulemembers table. If there is not a match in the modulemembers table then the custid will be NULL. 

SELECT c.id, c.name 
FROM customers c
    LEFT OUTER JOIN modulemembers m ON c.customers_id = m.cust_id 
WHERE m.custid IS NULL
ORDER BY c.name

Upvotes: 2

Taryn
Taryn

Reputation: 247710

You can use NOT EXISTS in the WHERE clause to get the result:

SELECT c.id, 
  c.name 
FROM customers c
WHERE not exists (select customer_id
                  from modulemembers m
                  where c.id = m.customer_id)
order by c.name

Upvotes: 4

Seer
Seer

Reputation: 5237

Try something like this:

   SELECT c.ID AS customerId,
          c.NAME AS fullName,
          m.ID AS memberId
     FROM Customers AS c
LEFT JOIN Modulemembers AS m ON m.customer_id = c.ID
    WHERE m.ID IS NULL;

Upvotes: 0

Related Questions