CODEWITHSUNDEEP
oracle-databaseoracle12c
AngelicCore
AngelicCore

Reputation: 1453

Can this query be written - what am I missing?

I am feeling stupid today but I have a query that I want to write but I am sensing that it might be out of my reach.

I will try to simplify the problem just for the sake of simplicity.

I have a table like this

[Table A]
ID | Class | Name
0      A     Sarah
1      B     Tom
2      C     Bob
3      A     Jen
4      A     John
5      A     Jack
6      B     Name1
7      B     Jack
8      B     Bob

I need to get all the records (the actual records not grouped by) for all the people that share the same class, but with the following restrictions:

Only get me the records where Jen, Jack & John share the same class (only those 3 no others and no duplicates).

That means the records will come as sets of 3.

So in the above example I would get

3      A     Jen
4      A     John
5      A     Jack

So far I've gotten:

SELECT Class, Name, COUNT(*)
FROM TableA
GROUP BY Class, Name
HAVING COUNT(*) = 3

But in addition to this not working correctly, I am unable to specify that the records must have the names Jen, Jack and John and nothing else.

EDIT: I was able to do it using inner query but it is a bit slow..If anyone knows a more optimal way please let me know:

SELECT ....
FROM TableA t1
join TableA  t2 on t1.class = t2.class
WHERE t1.name = 'Jen' AND t2.name = 'John'
AND t1.class IN(SELECT class FROM TableA t3 WHERE t3.name = 'Jack')))

I could probably have included t3 with the original query but I prefer this way for readability.

Not sure if this is the exact answer but the query I was looking for worked using this style and I tried to write the simple one using the same method for anyone stuck like I was.

Upvotes: 1

Views: 50

Answers (1)

Boneist
Boneist

Reputation: 23588

If you're after the classes where your three people all shared the same class, even if there were other people attending, then you were pretty close with your query, except you were missing the WHERE clause to restrict the rows to the names that you were interested in.

Plus, seeing as you didn't want to collapse the rows ("the actual records, not grouped by"), then you're after an analytic COUNT, not an aggregate.

Therefore, what you're after would look something like:

WITH table_a AS (SELECT 0 ID, 'A' CLASS, 'Sarah' NAME FROM dual UNION ALL
                 SELECT 1 ID, 'B' CLASS, 'Tom' NAME FROM dual UNION ALL
                 SELECT 2 ID, 'C' CLASS, 'Bob' NAME FROM dual UNION ALL
                 SELECT 3 ID, 'A' CLASS, 'Jen' NAME FROM dual UNION ALL
                 SELECT 4 ID, 'A' CLASS, 'John' NAME FROM dual UNION ALL
                 SELECT 5 ID, 'A' CLASS, 'Jack' NAME FROM dual UNION ALL
                 SELECT 6 ID, 'B' CLASS, 'Name1' NAME FROM dual UNION ALL
                 SELECT 7 ID, 'B' CLASS, 'Jack' NAME FROM dual UNION ALL
                 SELECT 8 ID, 'B' CLASS, 'Bob' NAME FROM dual)
-- End of mimicking your table_a with data in it
-- See query below:
SELECT ID,
       CLASS,
       NAME
FROM   (SELECT ID,
               CLASS,
               NAME,
               COUNT(NAME) OVER (PARTITION BY CLASS) name_cnt
        FROM   table_a
        WHERE  NAME IN ('Jen', 'Jack', 'John'))
WHERE  name_cnt = 3;

You'll note that analytic functions don't have an equivalent HAVING clause that aggregate functions do, which means that you have to do the filtering in an outer query. Hopefully you can see that my query is similar to yours, though?

Upvotes: 2

Related Questions