CODEWITHSUNDEEP
sqlms-access
Alex
Alex

Reputation: 6665

Selecting joining records where all rows have a field equalling false

I have two tables: Customers and Addresses. There can only be 1 customer record, but a customer may have multiple addresses. Each address has true/false field called "active".

I'm trying to design a query that selects any customers that don't have an active address. So customers with address records that are all marked "active = false", or have no address records at all.

I'm working in Access for this, so the SQL needs to be MS friendly. However I am interested to know the general SQL technique to do this kind of selection.

Edit: Table Structure

Customers

CustomerID, CustomerName, CustomerDoB

Addresses

AddressID, AddressName, AddressPostcode, CustomerID, Active

Upvotes: 1

Views: 111

Answers (3)

fthiella
fthiella

Reputation: 49079

This works in Ms-Access:

SELECT Customers.*
FROM Customers LEFT JOIN Addresses ON (Customers.customer_id = Addresses.customer_id AND Addresses.Active = TRUE)
WHERE Addresses.customer_id is null

using a left join I'm selecting all Customers and I'm trying to join each customer with an active address in the Addresses table. If the join doesn't succeed, Addresses.customer_id will be null.

Upvotes: 1

Matt Donnan
Matt Donnan

Reputation: 5003

This should point you in the right direction, without the table schema I've made some assumptions:

SELECT * FROM Customers
WHERE ID Not In (SELECT CustomerID FROM Addresses WHERE Active = -1)

This assumes an ID in the customer table and a CustomerID in the addresses table

Upvotes: 2

diederikh
diederikh

Reputation: 25281

select customer_id
from customers c
where not exists (select 1
                  from   addresses a
                  where  a.customer_id = c.customer_id
                  and    a.active = true
                 )

Upvotes: 3

Related Questions