0

I have a table of customer data. I will be joining it to a location table. Customer ID is distinct but Location ID is not because multiple customers can belong to one location. Each customer is identified as having an "Active" or "Inactive" status. Each customer is also identified as whether he or she is the location's Billing Contact.

I need to find all the Location ID values where at least one of these three conditions is true:

  1. No customer is listed as the Billing Contact.
  2. All customers listed as the Billing Contact have an "Inactive" status.
  3. All customers have an "Inactive" status.

I tried something like the query below (the examples have been simplified), and I know it can't be right, but I can't see exactly how because I get an error message about invalid columns "in the HAVING clause because it is not ctonained in either an aggregate function or the GROUP BY clause." I can put those two additional columns into the GROUP BY clause, but then I'm doing three groups when I'm pretty sure I only want to do one group.

SELECT DISTINCT
  vLocation.LocationName
  ,vLocation.LocationID
  ,vCustomer.LocationID
  ,vCustomer.CustomerID
  ,vCustomer.CustomerName
  ,vCustomer.Status
  ,vCustomer.BillingContact
  ,vCustomer.Email
  ,vCustomer.Status
FROM
  DB.vLocation
  LEFT OUTER JOIN DB.vCustomer
    ON vLocation.LocationID = vCustomer.LocationID
WHERE
  vLocation.Status = 'Active'
  AND (vCustomer.CustomerID IS NULL
    OR vCustomer.CustomerID IN (
  SELECT DISTINCT
    vCustomer.CustomerID
  FROM
    DB.vCustomer
  GROUP BY
    vCustomer.CustomerID
  HAVING
    vCustomer.BillingContact = 'False'
    OR (vCustomer.BillingContact = 'True'
      AND vCustomer.Status = 'Inactive')
    OR vCustomer.Status = 'Inactive'
  )
)

A coworker suggested asking an AI for help, and it suggested this, which allowed me to get data, but my results were wrong because locations with active billing contacts were included.

HAVING 
  SUM(CASE WHEN BillingContact = 'False' THEN 1 ELSE 0 END) > 0
  OR 
  SUM(CASE WHEN BillingContact = 'True' AND Status = 'Inactive' THEN 1 ELSE 0 END) > 0

Here is a sample table. Location ID 004 has no active customers and should be included in the results. Same with Location ID 085 even though one of the customers is the Billing Contact. Location ID 091 has an active customer who is not the Billing Contact and an inactive customer who is the Billing Contact. This Location ID should be included in the results. Location ID 092 has two active customers but neither one is the Billing Contact; it should be included in the results. Location IDs 100 and 101 should be entirely EXCLUDED from the results because AT LEAST ONE of them is active and is the Billing Contact.

Location ID Customer ID Customer Name Status Billing Contact
004 003 Bri Inactive FALSE
004 004 Rac Inactive FALSE
004 005 Tam Inactive FALSE
085 157 Ari Inactive FALSE
085 158 Hel Inactive TRUE
085 159 Ran Inactive FALSE
091 167 Jud Inactive TRUE
091 168 Ste Active FALSE
092 169 Izz Active FALSE
092 170 Kur Active FALSE
100 183 Ash Active FALSE
100 184 Bre Active TRUE
101 185 Cec Active FALSE
101 186 Emi Active FALSE
101 187 Eri Active TRUE
101 188 Jen Active FALSE
101 189 Jud Inactive TRUE
101 190 Mel Active FALSE
Improve this question
5
  • 3
    1) Please provide a minimal reproducible example with sample data and desired results (with emphasis on minimal, i.e. remove any columns and joins that don't affect the actual problem you are trying to solve). 2) Tag the RDBMS you are using. Commented Aug 8 at 22:07
  • 3
    A side note advice: Whenever you think you need SELECT DISTINCT you are very likely wrong. SELECT DISTINCT is very rarely needed and almost always an indicator for an inappropriate query. Commented Aug 8 at 22:21
  • Avoid use of AI. In addition to being unwelcome on Stack Overflow, it will just rot your brain by making you think your already too complex query is the way to go, and adding complexity to it. Human brains (if correctly presented the problem) will probably find a smart, simple, understandable solution. So first fullfill Dale K's request. Presenting us a simple, reduced dataset and what you try to achieve, you may even be able to make the problem clear to yourself (and find a solution). Commented Aug 8 at 22:31
  • As to your subquery: You select from vCustomer and group by CustomerID. But there should be just one row per customer ID in the customer table/view. (Is the v in vCustomer for "view"?) So either there is a flaw in your database or the subquery makes no sense. Commented Aug 8 at 22:32
  • 1
    Actually AI can be quite good at solving these kind of problems, but you need to be using the right model, you need to provide it with the right prompts and the right sample data and desired results - which all requires you understand the problem well enough to be able to solve it yourself 😜 Commented Aug 8 at 22:39

2 Answers 2

Reset to default
1

We have these conditions:

No customer is listed as the Billing Contact
OR
All customers listed as the Billing Contact have an "Inactive" status
OR
All customers have an "Inactive" status.`

This can be shortened to:

No 'Active' customers listed as the Billing Contact

This leads to this simple query:

SELECT *
FROM db.vlocation l
WHERE NOT EXISTS
(
  SELECT NULL
  FROM db.vcustomer c
  WHERE c.locationid = l.locationid
  AND c.billingcontact = 'True' 
  AND c.status = 'Active'
);

If vcustomer.locationid is not nullable, then you can even use a slightly shorter NOT IN query:

SELECT *
FROM db.vlocation
WHERE locationid NOT IN
(
  SELECT locationid l
  FROM db.vcustomer
  WHERE l.billingcontact = 'True' 
  AND l.status = 'Active'
);
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Your condition 2. is totally included in either condition 1. (if there's no billing contact, all of them (0) have inactive status. Or active. Whatever) or condition 3. (if all customers are inactive, well, even possible billing contacts will be inactive). So you just have to handle 1. and 3. to get 2. "for free".

Additionally your boolean logic could be inverted as "as soon as I have one active billing contact, discard the location", with a left anti join (or any more efficient method, but you didn't mention your RDBMS so we can't help):

SELECT vLocation.LocationID
FROM
  DB.vLocation
  LEFT OUTER JOIN DB.vCustomer
    ON vLocation.LocationID = vCustomer.LocationID AND vCustomer.BillingContact = 'True' AND vCustomer.Status = 'Active'
WHERE
  vCustomer.CustomerID IS NULL

Up to you to then use this list of locations in a subquery, to then display their current non-matching customers.
(from your SELECT DISTINCT vLocation.LocationName,vLocation.LocationID,vCustomer.LocationID,vCustomer.CustomerID,…/*lots of vCustomer.columns */ it looks like you're interested in the customers, and I doubt you'd want to display the boring NULL columns for the non-matches, so this must probably some kind of list of the candidates to become the active billing contact? Please mention it in your question)

(again, provide a small example with 4 locations in your question, and the expected results, so that we can see if we can make your query work)

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.