Query to check for items in a partition group

Question

I have a dataset that resembles the following format.

GROUP | Element
---------------
  1 | A
  1 | B
  1 | C
  2 | A
  3 | B
  4 | A
  4 | B
  5 | A
  5 | C

I would like to make sure that both A AND B elements exist in each of the groups. Ideally, I would only return groups that have both elements. In the above example, I would like to return only GROUP 1 and GROUP 4.

EDIT:
Sorry, I shouldn't have meant that “A” and “B” were the only options. Is it possible to look specifically at the existence of certain meanings, such as "A" and "B"? There may be other possible values. I updated the sample data to reflect this.

Answer 1

Relational Division, GROUP Element.

• SQL

Query:

SELECT  a.*
FROM    TableName a
WHERE   EXISTS
        (
            SELECT  1
            FROM    TableName b
            WHERE   a."GROUP" = b."GROUP" AND
                    b."ELEMENT" IN ('A','B')
            GROUP   BY b."GROUP"
            HAVING  COUNT(*) = 2
        )

• SQLFiddle Demo

╔═══════╦═════════╗
║ GROUP ║ ELEMENT ║
╠═══════╬═════════╣
║     1 ║ A       ║
║     1 ║ B       ║
║     1 ║ C       ║
║     4 ║ A       ║
║     4 ║ B       ║
╚═══════╩═════════╝

GROUP,

SELECT  "GROUP"
FROM    TableName b
WHERE   "ELEMENT" IN ('A','B')
GROUP   BY "GROUP"
HAVING  COUNT(*) = 2

• SQLFiddle Demo

╔═══════╗
║ GROUP ║
╠═══════╣
║     1 ║
║     4 ║
╚═══════╝

Answer 2

, ,

SELECT group, COUNT(DISTINCT(element))
FROM table
group by group
having COUNT(DISTINCT(element)) = 2

, COUNT(DISTINCT(element))!

Answer 3

(ID) ELEMENT (), 2 . HAVING, " 2 ELEMENTS".

SELECT GROUP, ELEMENT 
FROM MyTable
WHERE GROUP in 
    (SELECT GROUP from MyTable
     GROUP BY GROUP 
     HAVING COUNT (DISTINCT ELEMENT)=2
     )

Answer 4

> SELECT ID,
>        ELEMENT FROM STACK WHERE ID IN
>     (SELECT ID
>      FROM
>        (SELECT id AS ID,
>                COUNT(DISTINCT(element)) AS B
>         FROM STACK
>         WHERE ELEMENT IN ('A',
>                           'B')
>         GROUP BY id HAVING COUNT(DISTINCT(element)) >1)A)   AND ELEMENT IN ('A',
>                   'B');

** id -

If we remove the "ELEMENT IN" condition, we can get all groups that have more than one element

Answer 5

Returns the group only elements A AND B

SELECT a.*
FROM Table a
WHERE EXISTS
(
    SELECT 1
    FROM Table b
    WHERE a.group = b.group
    GROUP BY b.group
   HAVING COUNT(distinct element) = 2
)
and a.element IN ('A','B')