Oracle SQL, am I doing a JOIN of myself correctly?

Question

Oracle

Hello! There is an MSEG table of the following form, where MBLNR is part of a composite key, and BWART is a simple field:

+--------+-------+
| MBLNR  | BWART |
+--------+-------+
| 000001 |   531 |
| 000001 |   561 |
| 000001 |   591 |
| 000002 |   531 |
| 000002 |   591 |
+--------+-------+

It is necessary to select all MBLNRs for which the BWART field takes the values ​​531, 561 and 591 - all three are required.
Thus, 000001 should be in the result, but 000002 should not (no BWART = 561).
For this I wrote the following code:

SELECT A.MBLNR

FROM MSEG A
   , MSEG B
   , MSEG C

WHERE

  A.MBLNR = B.MBLNR AND
  A.MBLNR = C.MBLNR AND

  A.BWART = '531' AND
  B.BWART = '561' AND
  C.BWART = '591'
  
  AND ROWNUM <= 10

Will it bring me the desired result? Maybe there is a better way?

Answer 1

as an option

select count(bwart), mblnr
from
(select distinct mblnr, bwart 
from mseg
where bwart in ('531', '561', '591'))
group by mblnr
having count(bwart) = 3

But, your version is just as true, and, perhaps, it will work faster.