SQL Query to loop through

Question

I have two tables TravelHistory and LikesToVisit.

Both have two fields, SSN and AirportId.

What is the query to find if a customer has visited all cities he likes to visit?.

Any help is much appreciated.

score 0 · Answer 1 · answered Sep 24 '17 at 20:54

0

This will give you non-distinct list of those who haven't traveled to all of their likes.

SELECT * FROM LikesToVisit AS lv LEFT OUTER JOIN TravelHistory AS th ON lv.SSN = th.SSN AND lv.AirportID = th.AirportID WHERE lv.SSN = NULL

Or , alternatively with a subquery

SELECT * FROM LikesToVisit AS lv WHERE lv.SSN NOT IN (SELECT SSN FROM TravelHistory AS th WHERE th.SSN = lv.SSN AND th.AirportID = lv.AirportID)

answered Sep 24 '17 at 20:54

cjkeilig

101
7

note that using `in` with most DB's is often very slow (especially when dealing with large record sets within the subquery!). Using joins is typically significantly faster from personal experience. – user2366842 Sep 25 '17 at 01:45

score 0 · Answer 2 · answered Sep 24 '17 at 20:58

This query will give all the cities which he liked to visit and not visited yet.

SELECT SSN, Airportid
  FROM LikesToVisit
MINUS
SELECT SSN, Airportid
  FROM TravelHistory

Selecting all the SSN that are not in the above result set will give you the list of people who has visited all he liked

SELECT SSN, 'Visited all he liked' 
  FROM Travelhistory 
 WHERE SSN not in (select distinct SSN 
                     FROM( SELECT SSN, Airportid
                             FROM LikesToVisit
                            MINUS
                           SELECT SSN, Airportid
                             FROM TravelHistory)

SQL Query to loop through

2 Answers2