SQL query issues - how to find answer?

Question

Tables:

Based on following information, create this question:

What is the name of the persons that have visited the city "London"?

Is this homework? (Important to give proper responses.) What have you tried? — Guffa, Feb 25 '15 at 17:38

score 0 · Answer 1 · edited Feb 25 '15 at 19:13

0

Select p.Name from person p,has_visited hs 
where p.ssn= hs.ssn and hs.city= 'LONDON'

edited Feb 25 '15 at 19:13

answered Feb 25 '15 at 17:38

akhil vangala

score 0 · Answer 2 · answered Feb 25 '15 at 17:39

0

Just use the in operator:

SELECT name
FROM   person
WHERE  ssn IN (SELECT ssn
               FROM   has_visited
               WHERE  city = 'London');

answered Feb 25 '15 at 17:39

Mureinik

I'm curious, why did you prefer this to join ? Is this a performance matter or just a preference ? – Jean-François Savard Feb 25 '15 at 17:40
1

It's a readability issue - this query declares that it's selecting each person (no more than) once, not some combination of people and visits. Consider if the schema would be extended to list **when** a person visited a city, and allow multiple visits. A query using a join would return that person twice. This one would continue functioning properly. – Mureinik Feb 25 '15 at 17:43

2 Answers2