Re: Basic Query Question - Mailing list pgsql-novice
From | Jude Lucien |
---|---|
Subject | Re: Basic Query Question |
Date | |
Msg-id | CAPRXF2p0iyfRR6vXpRy14Rz+sKbVZa-ZR6W5ZLx4qqSoXMgTuA@mail.gmail.com Whole thread Raw |
In response to | Re: Basic Query Question (Ramy Abdel-Azim <ramy.abdel-azim@startdatelabs.com>) |
Responses |
Re: Basic Query Question
Re: Basic Query Question |
List | pgsql-novice |
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 >>>> >>>> >>>> >> >> > -- "None are more hopelessly enslaved than those who falsely believe they are free" -- Johann Wolfgang von Goethe
pgsql-novice by date: