Re: Basic Query Question

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(dot)abdel-azim(at)startdatelabs(dot)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(dot)abdel-azim(at)startdatelabs(dot)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(dot)abdel-azim(at)startdatelabs(dot)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(at)postgresql(dot)org)
>>>>>> To make changes to your subscription:
>>>>>> http://www.postgresql.org/mailpref/pgsql-novice
>>>>>
>>>>>
>>>
>
>