postgresql - SQL SUBQUERY ON SELF -

postgresql - SQL SUBQUERY ON SELF -

i have table next data:

licence_number date_of_birth organisation

i want query where:

get licence_numbers , dobs in organisation1 same licence numbers , dobs in organisation2.

i know cant hard, im struggling.

you can grouping license_number , date_of_birth organization set either of 2 interesting organizations, , count how many distinct organizations there in group.

if there 2 out of 2 possible in single group, have hit.

select license_number, date_of_birth mytable organisation in ('organisation1', 'organisation2') grouping license_number, date_of_birth having count(distinct organisation) = 2;

...or can utilize intersect;

select license_number, date_of_birth mytable organisation = 'organisation1' intersect select license_number, date_of_birth mytable organisation = 'organisation2'

an sqlfiddle test both.

sql postgresql