Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Unanswered: Query to return records with no related records in another table

    Hi everyone

    I'm trying to write a query and having problems.

    I have two tables, one is effectively a master table with customer details, the other is a detail tables with items relating to the customer.

    I want to return a recordset containing all the customers who have no detail records at all. I just can't get my head around this and would appreciate any help people can give.

    Thank in anticipation.

    Jon

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    All customers with orders:
    Code:
    SELECT *
    FROM   customers
     INNER
      JOIN orders
        ON orders.customer_id = customers.customer_id
    All customers with or without orders:
    Code:
    SELECT *
    FROM   customers
     LEFT
      JOIN orders
        ON orders.customer_id = customers.customer_id
    Notice any common differences for those customers without orders? Because we can't match an order record to them we get a whole load of NULL valued columns that would contain information from the orders table.

    So if we just want to show those:
    Code:
    SELECT *
    FROM   customers
     LEFT
      JOIN orders
        ON orders.customer_id = customers.customer_id
    WHERE  orders.customer_id IS NULL
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Here's another method...

    All customers with orders:
    Code:
    SELECT *
    FROM   customers
    WHERE  EXISTS (
       SELECT *
       FROM   orders
       WHERE  orders.customer_id = customers.customer_id
      )
    Now for those without:
    Code:
    SELECT *
    FROM   customers
    WHERE  NOT EXISTS (
       SELECT *
       FROM   orders
       WHERE  orders.customer_id = customers.customer_id
      )
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2012
    Posts
    2

    Smile

    Thanks guys for your help. I went with George's as it looked simpler and worked a dream first time.

    Huge thanks again.

    Jon

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •