Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table art(
   artnr INT NOT NULL,
   PRIMARY KEY ( artnr )
);



create table ean(
   ean_id INT,
   ean INT NOT NULL,
   unit_artnr INT NOT NULL,
   typ varchar(4) NOT NULL,
   PRIMARY KEY ( ean_ID )
);
insert into art (artnr) values (1234556);
1 rows affected

insert into ean (ean_id, ean, unit_artnr, typ) values ('1','1234','1234556', 'LE4');
1 rows affected

insert into ean (ean_id, ean, unit_artnr, typ) values ('2','5678','1234556', 'LE4');
1 rows affected

insert into ean (ean_id, ean, unit_artnr, typ) values ('3','12334512334','1234556', 'AT3');
1 rows affected

insert into ean (ean_id, ean, unit_artnr, typ) values ('4','5671234568','1234556', 'AT3');
1 rows affected

insert into art (artnr) values (23455);
1 rows affected

insert into ean (ean_id, ean, unit_artnr, typ) values ('5','7771234568','23455', 'LE4');
1 rows affected

insert into ean (ean_id, ean, unit_artnr, typ) values ('6','7771234568','23455', 'LE4');
1 rows affected

insert into ean (ean_id, ean, unit_artnr, typ) values ('7','7771234568','23455', 'AT3');
1 rows affected

insert into ean (ean_id, ean, unit_artnr, typ) values ('8','771234568','23455', 'AT3');
1 rows affected

SELECT art.*, EAN.*
   FROM art join 
   (
   select ean_id,ean,unit_artnr,typ,
   row_number() over(partition by typ order by ean_id)rn from ean
   ) EAN on ART.ARTNR = ean.unit_artnr
   WHERE 
    EAN.typ = 'LE4' and EAN.rn=1


ARTNR
EAN_ID
EAN
UNIT_ARTNR
TYP
RN


1234556
1
1234
1234556
LE4
1


SELECT ART.*, EAN.*
FROM ART,EAN 
WHERE ART.ARTNR = ean.unit_artnr
and ean.typ = 'LE4';


ARTNR
EAN_ID
EAN
UNIT_ARTNR
TYP


1234556
1
1234
1234556
LE4

1234556
2
5678
1234556
LE4

23455
5
7771234568
23455
LE4

23455
6
7771234568
23455
LE4

ARTNR	EAN_ID	EAN	UNIT_ARTNR	TYP
1234556	1	1234	1234556	LE4
1234556	2	5678	1234556	LE4
23455	5	7771234568	23455	LE4
23455	6	7771234568	23455	LE4