I have table with next structure:

ID | NAME | ORGANIZATION_ID | CITY_ID | COUNTRY_ID
---------------------------------------------------
1  | JOE  |    1            |   null  |  5
2  | JACK |    null         |   3     |  5
3  | TOM  |    1            |   null  |  3

Now if i provide organizationId and cityId as result I need to get countryId=5 as common value, because for both provided values there is rows with countryId, and for countryId=3 there is only Tom with value in column organizationId, and there are no one with value from cityId column

I´ve tried with this query:

select distinct(country_id) from employee where organization_id = 1 or city_id=3;

but as result I get 5 and 3.

How can I accomplish this to get only 5 as result?

CodePudding user response：

SELECT country_id
FROM employee
WHERE organization_id = 1 OR city_id = 3
GROUP BY country_id
HAVING COUNT(organization_id) > 0 AND COUNT(city_id) > 0

db<>fiddle

CodePudding user response：

I think you are trying to get the intersection of the country IDs with organization=1 and the country IDs with city=3.

I could think of the following queries. All of them return only country_id = 5.

SELECT
  country_id
FROM
  employee
WHERE
  organization_id = 1
  AND country_id IN (SELECT country_id FROM employee WHERE city_id=3)

SELECT
  country_id
FROM
  employee
WHERE
  organization_id = 1
INTERSECT

SELECT
  country_id
FROM
  employee
WHERE
  city_id=3

SELECT
  e1.country_id
FROM
  employee e1
INNER JOIN
  employee e2
ON
  e1.organization_id = 1
  AND e2.city_id = 3
  AND e1.country_id = e2.country_id