Re: Basic Query Question - Mailing list pgsql-novice
From | Ramy Abdel-Azim |
---|---|
Subject | Re: Basic Query Question |
Date | |
Msg-id | 4EDFE07C.7020305@startdatelabs.com Whole thread Raw |
In response to | Re: Basic Query Question (Ramy Abdel-Azim <ramy.abdel-azim@startdatelabs.com>) |
List | pgsql-novice |
try this: SELECT DISTINCT bike.bike_id FROM bike JOIN booking ON bike.bike_id=booking.bike_id WHERE bike.model='Kona Dew SEFRR' AND booking.booking_date='2011-11-20' ; On 12/7/11 4:45 PM, Ramy Abdel-Azim wrote: > 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: