How to Check ID Column to see if it exists, based on having clause

Question

I have an existing question, that I thought would lead me my answer, but now i'm left with another question, and i'm not sure how to solve it. My original quesiton is here: How to find Invoice detail counter column not in numeric order In my table I have the following information when I run the query

Select * from Invoice_detail where Description like 'Service%'

Result:

Detail_ID      date_Created       Invoice_id       Description
   1           1/1/18 12:02:03       1            Service 1
   2           1/1/18 12:02:04       1            Service 2 
   3           1/1/18 12:02:05       1            Service 3
   4           1/1/18 12:06:03       2            Service 1
   5           1/1/18 12:06:04       2            Service 2 
   6           1/1/18 12:06:05       2            Service 3
   7           1/1/18 12:08:03       3            Service 1
   8           1/1/18 12:08:04       3            Service 2 
   9           1/1/18 12:08:05       3            Service 3
  10           1/1/18 12:12:03       4            Service 1
  12           1/1/18 12:12:05       4            Service 3
  13           1/1/18 12:15:05       5            Service 1
  15           1/1/18 12:15:05       5            Service 2

Select * from Invoice_Detail returns:

Detail_ID      date_Created       Invoice_id       Description
   1           1/1/18 12:02:03       1            Service 1
   2           1/1/18 12:02:04       1            Service 2 
   3           1/1/18 12:02:05       1            Service 3
   4           1/1/18 12:06:03       2            Service 1
   5           1/1/18 12:06:04       2            Service 2 
   6           1/1/18 12:06:05       2            Service 3
   7           1/1/18 12:08:03       3            Service 1
   8           1/1/18 12:08:04       3            Service 2 
   9           1/1/18 12:08:05       3            Service 3
  10           1/1/18 12:12:03       4            Service 1
  12           1/1/18 12:12:05       4            Service 3
  13           1/1/18 12:15:05       5            Service 1
  14           1/1/18 12:15:05       5            Test
  15           1/1/18 12:15:05       5            Service 2

The query for my initial question - I'm running is:

select id.invoice_id
from invoice_detail id
where Description like ('Service%')
group by id.invoice_id
having count(*) <> max(id.detail_id) - min(id.detail_id) + 1;

This query, which was answered in my previoius question is doing exactly what I asked for - However, I found some special cases, where this issue pops up. I'd only like to return invoice_ID 4 ONLY (above) In this Case - Detail_ID '11' does not exist and was presumably deleted. I do not want to see Invoice_id 5, since the detail_id Exists, just not for that ticket.

I'm almost thinking I need something like this, too: SQL: find missing IDs in a table

I edited the question. My query will return both 4 and 5. Notice the "where" clause — Shmewnix, Jan 31 '18 at 00:10
not following. **return invoice_ID 4 ONLY in the event that Detail_ID 11, does NOT exist in the table. I do not want to see Invoice_id 5, since the detail_id Exists, just not for that ticket.** — RoMEoMusTDiE, Jan 31 '18 at 00:13

Gordon Linoff · Answer 1 · 2018-01-31T02:04:46.733

0

If I understand correctly, you can just remove the where:

select id.invoice_id
from invoice_detail id
group by id.invoice_id
having count(*) <> max(id.detail_id) - min(id.detail_id) + 1 and
       sum(case when Description not like 'Service%' then 1 else 0 end) = 0;

The second condition makes sure that all the rows that the service that you want.

edited Jan 31 '18 at 02:04

answered Jan 31 '18 at 00:10

Gordon Linoff

1,242,037
58
646
786

The where clause is needed, as I only want to see specific results sets of where a Detail_ID was deleted - Not necessarily used by another ticket_ID. So basically, I want to know - when a ticket has only services that I filter by is not sequential AND that Detail_ID(s) that would make it sequential don't exist in the table. – Shmewnix Jan 31 '18 at 00:13

tysonwright · Answer 2 · 2018-01-31T16:52:34.757

WHERE filters what goes into the aggregates, HAVING filters the aggregates themselves. You are using HAVING to do something that really is a WHERE, so try this (edited to note: I just tested this, and it does work):

select id.invoice_id
from invoice_detail id
where Description like ('Service%')
and id.invoice_id in 
(
select id.invoice_id
from invoice_detail id
group by id.invoice_id
having count(*) <> max(id.detail_id) - min(id.detail_id) + 1
)
group by id.invoice_id;

Hope this helps.

How to Check ID Column to see if it exists, based on having clause

2 Answers2