Обсуждение: UNION or LEFT JOIN?
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,
Louis-David, <br /><br />Please advice me,<br /><br />if some cabin doesn't have a price i.e. it is not available, is theresome way to tell<br />which cruise it belongs to?<br /><br />You have PRICE table which seems to me to be an associativetable between cruise and cabin, is this correct?<br /><br />But, if the price doesn't have a register for thatpair (îd_cabin,id_cruise), how do you know that cabin belongs to that cruise, in this case, cruise nº 1?<br /><br />Best,<br/>Oliveiros<br /><br /><div class="gmail_quote"> 2010/2/16 Louis-David Mitterrand <span dir="ltr"><<a href="mailto:vindex%2Blists-pgsql-sql@apartia.org"target="_blank">vindex+lists-pgsql-sql@apartia.org</a>></span><br /><blockquoteclass="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:1ex;">Here is the basic schema:<br /><br /> -------------------------->id_ship>-----------------------<br/> | |<br /> [SHIP]->id_ship->[CABIN]->id_cabin->[PRICE]<-id_cruise<-[CRUISE]<br /><br /> It's a databaseof cruise prices.<br /><br /> Each 'price' object has a reference to 'cabin' and 'cruise'<br /><br /> 'cabin' belongsto a 'ship', so does 'cruise'<br /><br /> I'm trying to select all cabins of cruise N°1 with prices OR nothing if<br/> there is no price (meaning cabin not available). I want all cabins<br /> listed, price or no price.<br /><br /> Alsowhen doing the query I don't have the id_ship, only the id_cruise.<br /><br /> What is the best way of doing it? UNIONor LEFT JOIN? I tried the latter<br /> without success and am unsure on how do do the former.<br /><br /> Thanks,<br/><font color="#888888"><br /> --<br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org"target="_blank">pgsql-sql@postgresql.org</a>)<br /> To make changes to your subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br />
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!
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
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,