add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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