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?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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
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
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0