SQL db query to exclude some entries

[jillapass]

I have 2 tables, one for clients, and one for client bookings
I want to find all clients that have never made a booking.
How do I do a query that links these 2 tables, but only selects those where there is not an entry in the 2nd table.

Thanks

[Colin Legg]

Here are couple of ways which should get you started.

 
--load some example data into 
--a couple of table variables
--to demonstrate the answer
DECLARE @TClients TABLE
    (
      [ClientID] [SMALLINT] ,
      [ClientName] [VARCHAR](30)
    )
DECLARE @TOrders TABLE
    (
      [OrderID] [SMALLINT] ,
      [ClientID] [SMALLINT]
    )
INSERT  INTO @TClients
        ( ClientID, ClientName )
VALUES  ( 1, 'Smith' ),
        ( 2, 'Jones' ),
        ( 3, 'JillaPass' ),
        ( 4, 'King' )
INSERT  INTO @TOrders
        ( OrderID, ClientID )
VALUES  ( 1, 1 ),
        ( 2, 1 ),
        ( 3, 2 ),
        ( 4, 1 )
/*
In this example JillaPass and King have never made an order.
Either of the below queries are a good starting point
*/
--query1
SELECT  c.ClientID ,
        c.ClientName
FROM    @TClients c
        LEFT OUTER JOIN @TOrders o ON o.ClientID = c.ClientID
WHERE   o.ClientID IS NULL
--query2
SELECT  c.ClientID ,
        c.ClientName
FROM    @TClients c
WHERE   NOT EXISTS ( SELECT NULL
                     FROM   @TOrders o
                     WHERE  c.ClientID = o.ClientID )