0

My table has 5 columns, one of those is named country_visited and another one is called name. In one of the rows I have Mexico and Jose, in another row I have Mexico and John. I want to retrieve back the country that both Jose and John have in common. 

SELECT country_visited 
FROM table
WHERE name = "Jose" AND name = "John" AND country_visited = country_visited
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Robert M.
  • 1
  • 1

3 Answers3

1

Just use HAVING Clause with COUNT(distinct name)=2:

SELECT country_visited 
  FROM table
 WHERE name in ('Jose','John')
 GROUP BY country_visited
 HAVING COUNT(distinct name)=2;

Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
1

Just join with itself

SELECT t1.country_visited
FROM table as t1  INNER JOIN table as t2 
ON t1.country_visited = t2.country_visited AND t1. name = 'Jose' AND t2.name = 'John'
Dmitry Reutov
  • 2,995
  • 1
  • 5
  • 20
  • About 1st point you are absolutely right, I edited my answer, about second one it doesn't look so, because t1 has only Jose and t2 has only John, so if name + country key is unique there should not be duplication – Dmitry Reutov Oct 06 '19 at 12:24
  • *"because t1 has only Jose and t2 has only John, so if name + country key is unique there should not be duplication "* Anyhow SQL is a declarative language, where you defined what you get not how to get it.. – Raymond Nijland Oct 06 '19 at 12:41
0

There are different answers to the query.

  1. Using group by having clause.

    SQL> SELECT country_visited
    FROM table
    WHERE name in ('Jose','John')
    GROUP BY country_visited
    HAVING COUNT(distinct name)>1;

  2. Using pivot (if your database supports)
    This will help you analyse the rows which are for Jose and John individually w.r.t country_visited

Khilesh Chauhan
  • 739
  • 1
  • 10
  • 36