Re: Basic Query Question - Mailing list pgsql-novice
From | Ramy Abdel-Azim |
---|---|
Subject | Re: Basic Query Question |
Date | |
Msg-id | 4EDFDE91.4020306@startdatelabs.com Whole thread Raw |
In response to | Re: Basic Query Question (Jude Lucien <jlucien@gmail.com>) |
Responses |
Re: Basic Query Question
|
List | pgsql-novice |
your syntax is wrong. look here: http://www.postgresql.org/docs/8.2/static/sql-select.html all joins come before the where clause. On 12/7/11 4:35 PM, Jude Lucien wrote: > SELECT DISTINCT bike.bike_id FROM bike WHERE bike.model='Kona Dew SE > FRR' AND booking.booking_date='2011-11-20' JOIN booking ON > bike.bike_id=booking.bike_id; > > returns a syntax error at JOIN > > SELECT DISTINCT bike.bike_id FROM bike LEFT JOIN booking ON > bike.bike_id = booking.bike_id AND booking.booking_date<> > '2011-11-20' WHERE bike.model = 'Kona Dew SE FRR'; > > returns 23 results - two of those results have a booking date of > 2011-11-20, so it should return 21 results. > > Cheers > > > On 7 December 2011 21:22, Ramy Abdel-Azim > <ramy.abdel-azim@startdatelabs.com> wrote: >> could you post the query you tried? >> >> >> On 12/7/11 4:20 PM, Jude Lucien wrote: >>> That query doesn't work for me as I can't make seem to add "WHERE >>> model='model'" to the query if I use a JOIN. >>> >>> >>> On 7 December 2011 21:16, Ramy Abdel-Azim >>> <ramy.abdel-azim@startdatelabs.com> wrote: >>>> try it out and let us know. >>>> we anxiously await your reply ;-) >>>> >>>> >>>> On 12/7/11 4:15 PM, Jude Lucien wrote: >>>>> I've just realised why it may not be working - bike_id is not the >>>>> primary key of booking. booking_id is. >>>>> >>>>> I was getting results, just not the correct ones. There are 23 bikes >>>>> of a certain model, two of which are booked on a certain date. My >>>>> query should then return 21 bike_id's. >>>>> >>>>> Can I even do a join if booking.bike_id is not the primary key? >>>>> >>>>> >>>>> On 7 December 2011 21:02, Ramy Abdel-Azim >>>>> <ramy.abdel-azim@startdatelabs.com> wrote: >>>>>> i'm not sure but i think not being explicit about the join causes psql >>>>>> to >>>>>> try to join on ID. >>>>>> >>>>>> What are you getting? an error or an empty result set? >>>>>> >>>>>> I would think you should do the join explicitly like this: >>>>>> >>>>>> select bike.bike_id from bike join booking on bike.bike_id = >>>>>> booking.bike_id where booking.date != '2011-11-20' >>>>>> >>>>>> >>>>>> On 12/7/11 3:55 PM, Jude Lucien wrote: >>>>>>> Sorry for the basic question, I can't seem to figure it out. >>>>>>> >>>>>>> I have two tables, booking and bike. Both have bike_id as primary >>>>>>> key. >>>>>>> >>>>>>> I am trying to return all bike_id's where bike.model='Kona' AND >>>>>>> booking.booking_date DOES NOT EQUAL 'date'; >>>>>>> >>>>>>> My query is as follows: >>>>>>> >>>>>>> SELECT bike.bike_id FROM bike,booking WHERE bike.model='model' AND >>>>>>> booking.booking_date!='2011-11-20' AND bike.bike_id=booking.bike_id; >>>>>>> >>>>>>> Any help appreciated! >>>>>>> >>>>>>> Jude >>>>>>> >>>>>>> >>>>>>> >>>>>> -- >>>>>> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) >>>>>> To make changes to your subscription: >>>>>> http://www.postgresql.org/mailpref/pgsql-novice >>>>> >>>>> >>> > >
pgsql-novice by date: