1

I have one table with two columns in it. First one is 'tourist_name' and second one is 'visited_places'. So I need a query which will give me following output : 'tourist_name' and the places which is not visited by tourist.

paulsm4
  • 114,292
  • 17
  • 138
  • 190
Mahi
  • 11
  • 1
  • 1
    Welcome to SO. Please take the [tour], read [ask], and edit the question to show the table DDL and example data and expected output and any attempts you have made. – OldProgrammer Mar 05 '21 at 17:23
  • The basic answer to your question is here: [How to select all records from one table that do not exist in another table?](https://stackoverflow.com/a/2686266/421195) – paulsm4 Mar 05 '21 at 17:25
  • And where is your source of places that _could_ have been visited but weren't? Looks like you are going to - at the very least - need a 'places' table . . . https://stackoverflow.com/help/minimal-reproducible-example – EdStevens Mar 05 '21 at 19:28
  • Are the places not visited by a tourist the ones that other have visited but this one hasn't? If so, please edit your question to clarify this. – hbejgel Mar 06 '21 at 19:38

1 Answers1

0

You can generate the rows using cross-join and then simply use minus clause to get your desired result -

WITH TAB AS (SELECT 1 TOURIST_NAME, 'B' VISITED_PLACE FROM DUAL UNION ALL
             SELECT 1, 'C' FROM DUAL UNION ALL
             SELECT 1, 'D' FROM DUAL UNION ALL
             SELECT 2, 'A' FROM DUAL UNION ALL
             SELECT 2, 'E' FROM DUAL)
SELECT TOURIST_NAME, VISITED_PLACE FROM (SELECT DISTINCT VISITED_PLACE FROM TAB),
              (SELECT DISTINCT TOURIST_NAME FROM TAB)
MINUS
SELECT TOURIST_NAME, VISITED_PLACE FROM TAB;

Link.

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
  • "MINUS" happens to be available in Oracle...but it's not standard across other major SQL implementations. I'd discourage using it except in "one off" situations... Look [here](https://stackoverflow.com/a/11197301/421195) – paulsm4 Mar 05 '21 at 22:12
  • Since the question is asked for Oracle 11g, I think my answer is valid. – Ankit Bajpai Mar 06 '21 at 08:46