0

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.

Improve this question
2
  • I'm not sure what you're asking for. You want unique rows where city1 is either "Joe A" or "Jane B"? If so, SELECT DISTINCT city2 FROM your_table_name WHERE city1='Joe A' OR city1 = 'Jane B'; Commented Aug 22, 2013 at 1:18
  • suppose if A lived in america,india,japan and australia and B lived in india,ghana,fiji and australia. if i want to find out which cities both A and B have lived in commonly(intersection). Commented Aug 22, 2013 at 1:23

4 Answers 4

Reset to default
2

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1 
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.

Improve this answer

2 Comments

A short explanation would improve this answer.
@Blorgbeard added an explanation.
1

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
Improve this answer

2 Comments

Does't the having imply you have to know how many cities would match? so if there was only one city on which both Joe A and Jane B shared, wouldn't this fail to return that intersection?
if count(city1)>=2,that means that both of joe A and Jane B have this city2 in common.
0

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.