Re: Basic Query Question

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

--
"None are more hopelessly enslaved than those who falsely believe they
are free"  -- Johann Wolfgang von Goethe