SQL Query - list all Customers who have no data

[Arg81]

Hi,

I'm trying to write an SQL query that lists all Customers that haven't had any work set to them.
I've got the basics, but there's a "middleman" table of Customer Sites.

This is a basic DB layout:

Customer (can have many) CustomerSites
Development
Samples

A customer site is set to the Dev or Samples table.

Here is what I have

SELECT     C.CustomerID, C.CustomerName 
FROM        Customer AS C 
LEFT OUTER JOIN CustomerSite AS CS ON C.CustomerID = CS.CustomerID 
LEFT OUTER JOIN Sample AS SR ON CS.CustomerSiteID = SR.CustomerSiteID
LEFT OUTER JOIN Development AS D ON CS.CustomerSiteID = D.CustomerSiteID
WHERE     (D.DevID IS NULL) AND (SR.SamID IS NULL)
ORDER BY C.CustomerName

So it looks at all the rows in the Devs, and all the rows in the Samples, and lists all Customer sites that aren't listed in them.
It then lists the Customer Name for these Customer Sites.


HOWEVER (this is where I've come undone so to say)

A customer can have many sites. If for instance the customer has 4 sites, and only 1 of them hasn't been used, I DONT want the Customer Name to show.
The query above will list the Customer Name even if other sites have been used.

 

[InertiaM]

I'd do it by incorporating subqueries. Write a query to give you just customers with work first. Say something like :-

SELECT
  C.CustomerID
FROM
(
(
  Customer AS C INNER JOIN CustomerSite AS CS ON C.CustomerID = CS.CustomerID
)
LEFT JOIN Sample AS SR ON CS.CustomerSiteID = SR.CustomerSiteID
)
LEFT JOIN Development AS D ON CS.CustomerSiteID = D.CustomerSiteID
WHERE
     (D.DevID IS NOT NULL)
OR
     (SR.SamID IS NOT NULL)
GROUP BY
  C.CustomerID

Then run another query to give you CustomerIDs NOT IN the first (unavailable) list.

SELECT
  C.CustomerID,
  C.CustomerName 
FROM
  Customer AS C LEFT JOIN
(
  [B]/* INSERT FIRST QUERY HERE */[/B]
) AS UnavailableCustomers ON C.CustomerID = UnavailableCustomers.CustomerID
WHERE
  UnavailableCustomers.CustomerID IS NULL

I hope I understood it correctly

 

[Arg81]

That's what I was thinking....

However, are subqueries good? I've just posted another question because I'm using subqueries on another part of the application, but not sure how performance-critical they are...

thanks for the tip though

 

[InertiaM]

I'm sure someone will correct me if I'm wrong, but subqueries are fine as long as you structure them correctly.

If you run

SELECT C.ID FROM C WHERE C.ID NOT IN (SUBQUERY)

that's badly structured, because it has to run the subquery for every instance of ID.

If you ran it as

SELECT C.ID FROM C LEFT JOIN (SUBQUERY) AS D ON C.ID = D.ID WHERE D.ID IS NULL

I believe it runs the subquery first (and once only) to create the join.

I'm not an expert on speed and overheads on SQL servers - but I use subqueries day in, day out!!

 

[Arg81]

works a treat, and surprisingly easy to put together (I use to hate sub queries)

thanks for the tips - I did try to add rep for you but apparently I've recently given you some and need to "spread the wealth" so to say a little more before giving you any more