Trying make join to get data about people flying on Mars

I'm having studying database, which contains information about different flights on spaceships. I will provide you with my database for better understanding.My datalogical model

The task is - find the amount of people that flew to a certain point on a certain ship. I do it with the code shown below:

select ship.shipname, destination.name as destination_name, count(person_id)
from person
        join flight_person on flight_person.PERSON_ID = person.ID
        join flight on flight.id = flight_person.flight_id
        join ship on flight.ship_id = ship.id
        join destination on flight.destination_id = destination.id
group by ship.shipname, destination.name;

The output is next:

But the problem is, that I want to get information about points and destinations that I didn’t visit, how can I modify my query to get this data. In my case with is destination Neptune and snip FirePower

CodePudding user response：

Probably, what you're looking for is called "FULL JOIN". In this case, you'll get an output, which contains all information about all ships and all planets. Consider following example:

select ship.shipname, destination.name as destination_name, count(person_id)
from person
    join flight_person on flight_person.PERSON_ID = person.ID
    join flight on flight.id = flight_person.flight_id
    full join ship on flight.ship_id = ship.id
    full join destination on flight.destination_id = destination.id
group by ship.shipname, destination.name;