Re: Basic Query Question

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(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
>>>>>>
>>>>>>
>>>>
>>
>>
>