pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUNION or LEFT JOIN?
5+ messages / 3 participants
[nested] [flat]
* UNION or LEFT JOIN?
@ 2010-02-16 14:08 Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-16 15:33 ` Re: UNION or LEFT JOIN? Oliveiros <oliveiros.cristina@gmail.com>
2010-02-16 21:38 ` Re: UNION or LEFT JOIN? Tim Landscheidt <tim@tim-landscheidt.de>
0 siblings, 2 replies; 5+ messages in thread
From: Louis-David Mitterrand @ 2010-02-16 14:08 UTC (permalink / raw)
To: pgsql-sql
Here is the basic schema:
-------------------------->id_ship>-----------------------
| |
[SHIP]->id_ship->[CABIN]->id_cabin->[PRICE]<-id_cruise<-[CRUISE]
It's a database of cruise prices.
Each 'price' object has a reference to 'cabin' and 'cruise'
'cabin' belongs to a 'ship', so does 'cruise'
I'm trying to select all cabins of cruise N°1 with prices OR nothing if
there is no price (meaning cabin not available). I want all cabins
listed, price or no price.
Also when doing the query I don't have the id_ship, only the id_cruise.
What is the best way of doing it? UNION or LEFT JOIN? I tried the latter
without success and am unsure on how do do the former.
Thanks,
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: UNION or LEFT JOIN?
2010-02-16 14:08 UNION or LEFT JOIN? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2010-02-16 15:33 ` Oliveiros <oliveiros.cristina@gmail.com>
2010-02-16 21:02 ` Re: UNION or LEFT JOIN? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 1 reply; 5+ messages in thread
From: Oliveiros @ 2010-02-16 15:33 UTC (permalink / raw)
To: pgsql-sql
Louis-David,
Please advice me,
if some cabin doesn't have a price i.e. it is not available, is there some
way to tell
which cruise it belongs to?
You have PRICE table which seems to me to be an associative table between
cruise and cabin, is this correct?
But, if the price doesn't have a register for that pair
(îd_cabin,id_cruise), how do you know that cabin belongs to that cruise, in
this case, cruise nº 1?
Best,
Oliveiros
2010/2/16 Louis-David Mitterrand
<vindex+lists-pgsql-sql@apartia.org<vindex%2Blists-pgsql-sql@apartia.org>
>
> Here is the basic schema:
>
> -------------------------->id_ship>-----------------------
> | |
> [SHIP]->id_ship->[CABIN]->id_cabin->[PRICE]<-id_cruise<-[CRUISE]
>
> It's a database of cruise prices.
>
> Each 'price' object has a reference to 'cabin' and 'cruise'
>
> 'cabin' belongs to a 'ship', so does 'cruise'
>
> I'm trying to select all cabins of cruise N°1 with prices OR nothing if
> there is no price (meaning cabin not available). I want all cabins
> listed, price or no price.
>
> Also when doing the query I don't have the id_ship, only the id_cruise.
>
> What is the best way of doing it? UNION or LEFT JOIN? I tried the latter
> without success and am unsure on how do do the former.
>
> Thanks,
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: UNION or LEFT JOIN?
2010-02-16 14:08 UNION or LEFT JOIN? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-16 15:33 ` Re: UNION or LEFT JOIN? Oliveiros <oliveiros.cristina@gmail.com>
@ 2010-02-16 21:02 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 0 replies; 5+ messages in thread
From: Louis-David Mitterrand @ 2010-02-16 21:02 UTC (permalink / raw)
To: pgsql-sql
On Tue, Feb 16, 2010 at 03:33:23PM +0000, Oliveiros wrote:
> Louis-David,
>
> Please advice me,
Hi Oliveiros,
> if some cabin doesn't have a price i.e. it is not available, is there some
> way to tell
> which cruise it belongs to?
In fact a cabin belongs to a ship and CAN be associated to a 'cruise'
event with a price(id_cruise,id_cabin) object.
> You have PRICE table which seems to me to be an associative table between
> cruise and cabin, is this correct?
Yes,
> But, if the price doesn't have a register for that pair
> (îd_cabin,id_cruise), how do you know that cabin belongs to that cruise, in
> this case, cruise nº 1?
I am trying to display a list of all cabins of a ship for a certain
cruise even if some prices are missing, so the user sees what cabins are
not available.
After much trial and error I was finally able to build a left join query
that works.
Thanks a lot for offering your help!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: UNION or LEFT JOIN?
2010-02-16 14:08 UNION or LEFT JOIN? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
@ 2010-02-16 21:38 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-02-16 21:43 ` Re: UNION or LEFT JOIN? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
1 sibling, 1 reply; 5+ messages in thread
From: Tim Landscheidt @ 2010-02-16 21:38 UTC (permalink / raw)
To: pgsql-sql
Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> wrote:
> Here is the basic schema:
> -------------------------->id_ship>-----------------------
> | |
> [SHIP]->id_ship->[CABIN]->id_cabin->[PRICE]<-id_cruise<-[CRUISE]
> It's a database of cruise prices.
> Each 'price' object has a reference to 'cabin' and 'cruise'
> 'cabin' belongs to a 'ship', so does 'cruise'
> I'm trying to select all cabins of cruise N°1 with prices OR nothing if
> there is no price (meaning cabin not available). I want all cabins
> listed, price or no price.
> Also when doing the query I don't have the id_ship, only the id_cruise.
> What is the best way of doing it? UNION or LEFT JOIN? I tried the latter
> without success and am unsure on how do do the former.
Was does "without success" mean? The objective seems to be
straight-forward:
- Select all cabins that belong to the ship that belongs to
the cruise id_cruise.
- Left join that with the prices of the cruise id_cruise.
Tim
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: UNION or LEFT JOIN?
2010-02-16 14:08 UNION or LEFT JOIN? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-16 21:38 ` Re: UNION or LEFT JOIN? Tim Landscheidt <tim@tim-landscheidt.de>
@ 2010-02-16 21:43 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
0 siblings, 0 replies; 5+ messages in thread
From: Louis-David Mitterrand @ 2010-02-16 21:43 UTC (permalink / raw)
To: pgsql-sql
On Tue, Feb 16, 2010 at 09:38:19PM +0000, Tim Landscheidt wrote:
> Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org> wrote:
>
> > Here is the basic schema:
>
> > -------------------------->id_ship>-----------------------
> > | |
> > [SHIP]->id_ship->[CABIN]->id_cabin->[PRICE]<-id_cruise<-[CRUISE]
>
> > It's a database of cruise prices.
>
> > Each 'price' object has a reference to 'cabin' and 'cruise'
>
> > 'cabin' belongs to a 'ship', so does 'cruise'
>
> > I'm trying to select all cabins of cruise N°1 with prices OR nothing if
> > there is no price (meaning cabin not available). I want all cabins
> > listed, price or no price.
>
> > Also when doing the query I don't have the id_ship, only the id_cruise.
>
> > What is the best way of doing it? UNION or LEFT JOIN? I tried the latter
> > without success and am unsure on how do do the former.
>
> Was does "without success" mean? The objective seems to be
> straight-forward:
>
> - Select all cabins that belong to the ship that belongs to
> the cruise id_cruise.
> - Left join that with the prices of the cruise id_cruise.
Definitely the way to go. As the real schema is quite a bit more
complicated I was struggling with very long statements, but finally
succeded with a simple left join.
Thanks,
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-02-16 21:43 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-16 14:08 UNION or LEFT JOIN? Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-16 15:33 ` Oliveiros <oliveiros.cristina@gmail.com>
2010-02-16 21:02 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
2010-02-16 21:38 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-02-16 21:43 ` Louis-David Mitterrand <vindex+lists-pgsql-sql@apartia.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox