Oracle SQL: SQL join with group by (count) and having clauses

Question

I have 3 tables

Table 1.) Sale
Table 2.) ItemsSale
Table 3.) Items

Table 1 and 2 have ID in common and table 2 and 3 have ITEMS in common.

I'm having trouble with a query that I have made so far but can't seem to get it right.

I'm trying to select all the rows that only have one row and match a certain criteria here is my query:

select *
from sales i
    inner join itemssales j on i.id = j.id
    inner join item u on j.item = u.item
where u.code = ANY ('TEST','HI') and
    i.created_date between TO_DATE('1/4/2016 12:00:00 AM','MM/DD/YYYY HH:MI:SS AM') and
    TO_DATE('1/4/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS PM')
group by i.id
having count(i.id) = 1

In the ItemSale table there are two entries but in the sale table there is only one. This is fine...but I need to construct a query that will only return to me the one record.

I believe the issue is with the "ANY" portion, the query only returns one row and that row is the record that doesn't meet the "ANY ('TEST', 'HI')" criteria. But in reality that record with that particular ID has two records in ItemSales.

I need to only return the records that legitimately only have one record.

Any help is appreciated.

--EDIT:


COL1 | ID
-----|-----
2    |  26
3    | 85
1    | 23
1    | 88
1    | 6
1    | 85

What I also do is group them and make sure the count is equal to 1 but as you can see, the ID 85 is appearing here as one record which is a false positive because there is actually two records in the itemsales table.

I even tried changing my query to j.id after the select since j is the table with the two records but no go.

--- EDIT

Sale table contains:
ID
---
85

Itemsales table contains:

ID | Position | item_id
---|----------|---------
85 |     1    |   6
85 |     2    |   7

Items table contains:

item_id | code
--------|------
   7    | HI
   6    | BOOP

The record it is returning is the one with the Code of 'BOOP'

Thanks,

Can you provide a sample of the output you currently obtain and what you would expect? — Danilo Tommasina, Jan 04 '16 at 07:13
So do you want records which have one and _only_ one type of code (`ANY`, `HI`) ? — Tim Biegeleisen, Jan 04 '16 at 07:19
Correct but it can't be in the itemssales table more than once either that is the trick, i thought the above would work it is not. — RobertC, Jan 04 '16 at 07:28
"sample data" means data for each of the tables involved, can you provide some please (not your existing query result) — Paul Maxwell, Jan 04 '16 at 07:28
Basically you are asking for all sales that contain only one item? I think you have to rework your question. It is indeed confusing and maybe leave the *believe, guessing* part out. Just say what you tried. What output you expect for which input. — Ely, Jan 04 '16 at 07:40
@RobertC - please try to name your objects consistently. Your question switched randomly between single and plural names. — APC, Jan 04 '16 at 07:48

score 1 · Answer 1 · answered Jan 04 '16 at 07:41

"I need to only return the records that legitimately only have one record."

I interpret this to mean, you only want to return SALES with only one ITEM. Furthermore you need that ITEM to meet your additional criteria.

Here's one approach, which will work fine with small(-ish) amounts of data but may not scale well. Without proper table descriptions and data profiles it's not possible to offer a performative solution.

with itmsal as    
            ( select sales.id 
              from itemsales
                    join sales on sales.id = itemsales.id 
              where sales.created_date >= date '2016-01-04'
              and sales.created_date < date '2016-01-05'
              group by sales.id having count(*) = 1)
select sales.*
       , item.*
from itmsal
     join sales on sales.id = itmsal.id
     join itemsales on itemsales.id = itmsal.id
     join items on itemsales.item = itemsales.item
where items.code in ('TEST','HI')

score 0 · Answer 2 · answered Jan 04 '16 at 09:08

I think you are trying to restrict the results so that items MUST ONLY have the code of 'TEST' or 'HI'.

select
     sales.*
from (
      select
          s.id
      from Sales s
          inner join Itemsales itss on s.id = itss.id
          inner join Items i on itss.item_id = i.item_id
      group by
          s.id
      where s.created_date >= date '2016-01-04'
          and s.created_date < date '2016-01-05'
      having
          sum(case when i.code IN('TEST','HI') then 0 else 1 end) = 0
     ) x
inner join sales on x.id = sales.id
... /* more here as required */

This construct only returns sales.id that have items with ONLY those 2 codes.

Note it could be done with a common table expression (CTE) but I prefer to only use those when there is an advantage in doing so - which I do not see here.

score 0 · Answer 3 · answered Jan 05 '16 at 08:43

If I get it correctly this may work (not tested):

select *
from sales s
inner join (
   select i.id, count( i.id ) as cnt
   from sales i
   inner join itemssales j on i.id = j.id
   inner join item u on j.item = u.item and u.code IN ('TEST','HI')
   where i.created_date between TO_DATE('1/4/2016 12:00:00 AM','MM/DD/YYYY HH:MI:SS AM') and
TO_DATE('1/4/2016 11:59:59 PM','MM/DD/YYYY HH:MI:SS PM')
   group by i.id
) sj on s.id = sj.id and sj.cnt = 1

Oracle SQL: SQL join with group by (count) and having clauses

3 Answers3