SQL Server : select from parent where id doesnt exist in either child tables

Question

I need to select all rows from our customer table where they have no rows in the call table and no rows in the call archive table. Seems simple, but I have wrapped myself up in knots trying to get the query running.

So the structure of the tables is below: customer is the parent, with call and call archive both linked to the customer_id.

Can anyone help me out here please!

CREATE TABLE [dbo].[customer]
(
    [customer_Id] [varchar](50) NOT NULL
    CONSTRAINT [PK_customer] 
        PRIMARY KEY CLUSTERED ([customer_Id] ASC)
)

CREATE TABLE [dbo].[call]
(
    [call_Id] [int] NOT NULL,
    [customer_Id] [int] NULL,
    [call_description] [varchar](50) NULL,

    CONSTRAINT [PK_call] 
        PRIMARY KEY CLUSTERED ([call_Id] ASC)
) 

ALTER TABLE [dbo].[call] WITH CHECK 
    ADD CONSTRAINT [FK_call_customer] 
        FOREIGN KEY([customerKey]) REFERENCES [dbo].[customer] ([customerkey])
GO

CREATE TABLE [dbo].[callArchive]
(
    [call_Id] [int] NOT NULL,
    [customer_Id] [int] NULL,
    [call_description] [varchar](50) NULL,

    CONSTRAINT [PK_call] 
        PRIMARY KEY CLUSTERED ([call_Id] ASC)
) 

ALTER TABLE [dbo].[call] WITH CHECK 
    ADD CONSTRAINT [FK_call_customer] 
        FOREIGN KEY([customerKey]) REFERENCES [dbo].[customer] ([customerkey])
GO

I tried doing a select count on the call_id columns using left outer joins but I am getting records in there that I was not expecting to see:

SELECT      
    COUNT(call.Call_id) AS Calls, 
    COUNT(callArchive.Call_id) AS Archive_Calls
FROM         
    customer 
LEFT OUTER JOIN
    call ON customer.customer_id = call.customer_id 
LEFT OUTER JOIN
    callArchive ON customer.customer_id = callArchive.customer_id
HAVING     
    ((COUNT(callArchive.Call_id) = 0) 
     AND (COUNT(call.Call_id) = 0)) 
ORDER BY 
    customer.customer_dateAdded DESC

Answer 1

Instead of faffing around with joins, a much simpler approach would be to use the not exists operator:

SELECT *
FROM   [customer] c
WHERE  NOT EXISTS (SELECT * 
                   FROM   [call]
                   WHERE  [call].customer_id = c.customer_id) AND
       NOT EXISTS (SELECT * 
                   FROM   [callArchive]
                   WHERE  [callArchive].customer_id = c.customer_id)

Answer 2

Faffing around with joins? Joins as 'not simple'? OK...now it's getting personal. :-P

SELECT * FROM   [customer] cu

LEFT JOIN call c on cu.customer_id = c.customerID    
LEFT JOIN callArchive ca on cu.customer_id = ca.customerID

WHERE c.customerID is null AND ca.customerID is null