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','7890123', 'LE4');
1 rows affected

SELECT art.*, EAN.*, 
   EAN.unit_artnr "EAN_ARTNR"
   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
EAN_ARTNR


1234556
1
1234
1234556
LE4
1
1234556