(Oracle SQL) Problems with joins and pairs of rows -
i have 3 tables
create table airships( idas number primary key, nameas varchar2(20), range number ); create table certificate( idem number not null, idas number not null, foreign key (idem) references employees(idem), foreign key (idas) references airships(idas) ); create table employees( idem number primary key, nameem varchar2(20), paycheck number );
i have find pairs of employees certified same airships range on 3000.
i know have use join, dont know how find pairs same table.
select e1.nameem, e2.nameem, a.nameas employees e1 join certificates c1 on e1.idem = c1.idem join certificates c2 on c1.idas = c2.idas , c1.idem < c2.idem join employees e2 on e2.idem = c2.idem join airships on a.idas = c1.idas a.range > 3000
the c1.idem < c2.idem
test keeps reporting employee pair himself, , avoids showing same pair twice (in opposite order).
Comments
Post a Comment