CODEWITHSUNDEEP
mysql
user2705577
user2705577

Reputation: 17

mysql query for selecting those two column values having same corresponding column value

i have a table like given below.

id        | city1   | city2       | distance
----------+---------+-------------+-----------
1         | Jane B  | japan       | 8
2         | Joe A   | florida     | 11 
3         | Joe F   | california  | 215 
4         | Jane A  | ghana       | 3
5         | Jane B  | florida     | 8
6         | Joe C   | jakarta     | 11 
7         | Joe F   | california  | 215 
8         | Joe A   | japan       | 3

Suppose if i want to find common city2 for jow A and jane B, how can i retrieve this in mysql.( the result will be japan and florida in this case).If i provide two values from city1 column,the result comes out to be a common values of both if exists from column city2.

Upvotes: 0

Views: 141

Answers (4)

Charaf JRA
Charaf JRA

Reputation: 8334

For your example , you can have your result this way : 

 SELECT city2 
 FROM table
 WHERE city1 = "Joe A" or city1 = "Jane B"
 group by city2
 Having count(city1)>=2

Upvotes: 1

xQbert
xQbert

Reputation: 35323

Select a.ID, a.City1, a.City2, a.distance
FROM table A
INNER JOIN table B on A.City2 = B.City2
AND A.City1 = 'JANE B' and B.City1 = 'Joe A'

The intent here is to execute a self join on the City names between both City2 values such that all like cities are returned, but only if city1 and city2 are of the interested values.

So using the data provided

  • Japan would map to Japan for Joe A and Jane B and be retained due to the limiting criteria
  • Florida would map to Florida for Joe A and Jane B and be retained due to the limiting criteria
  • California would map to California... but since the limiting criteria of Jane B and Joe A are not met, it would be excluded from the results.

This method gives you the capability of returning any values from the tables for either value of City1 (Jane B or Joe A), not just a city.

It uses the method of an inner join on itself which represents the intersection of the sets as implied desired from the comments.

Upvotes: 1

Jeff Scott
Jeff Scott

Reputation: 326

Depending on your data distribution subqueries might help or at least seem cleaner.

select A.city2
from 
(
    select distinct city2
    from t
    where t.city1 = 'Joe A'
) A
inner join 
(
    select distinct city2
    from t
    where t.city1 = 'Jane B'
) B
on A.city2 = B.city2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can do it this way:

select city2
from t
group by city2
having sum(city1 = 'Joe A') > 1 and
       sum(city1 = 'Jane B') > 1;

Upvotes: 2

Related Questions