Thread: complex join question

Hi,

Here is my schema:

cruise_line -> ship(id_line) -> cruise_type(id_ship) -> cruise(id_cruise_type) -> price(id_cruise, id_currency) <-
currency(USD,GBP,EUR,CAD)
 

(a 'cruise' is a 'cruise_type' + a date)

I am trying to display a count of cruise's for each ship and each
currency even if that count is 0.

But I am having trouble building the query, as some 'cruise's might not
(yet) have a 'price' in all currencies and so no link to 'currency'.

Thanks,

Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> wrote:

> Here is my schema:

> cruise_line -> ship(id_line) -> cruise_type(id_ship) -> cruise(id_cruise_type)
>     -> price(id_cruise, id_currency) <- currency (USD,GBP,EUR,CAD)

> (a 'cruise' is a 'cruise_type' + a date)

> I am trying to display a count of cruise's for each ship and each
> currency even if that count is 0.

> But I am having trouble building the query, as some 'cruise's might not
> (yet) have a 'price' in all currencies and so no link to 'currency'.

You're probably looking for a "LEFT JOIN".

Tim