Re: Basic Query Question

This is it. There are other instances of bike_id in the booking
database that have different booking dates.

I can find bike_id's that match my specified booking date, but then I
need to run a second query to find the bike_id's that are not booked
on that date.

Is there any way to join the two queries into one?

Thanks for all the help so far.

On 7 December 2011 21:53, Bret Fledderjohn <freelancer317(at)gmail(dot)com> wrote:
> Hi Jude,
>
> On 7 December 2011 16:35, Jude Lucien <jlucien(at)gmail(dot)com> 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
>
>
> JOIN is in the wrong location.  Needs to be before the WHERE clause.
>>
>>
>> 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';
>
>
> You're comparing the booking_date in the JOIN and you should be doing this
> in the WHERE clause since that will select only the booking.bike_id's rented
> on all other dates.
>>
>>
>> returns 23 results - two of those results have a booking date of
>> 2011-11-20, so it should return 21 results.
>>
>> Cheers
>>
> --
>
> - Bret
> ____________________________________________
> "Why should I fret in microcosmic bonds
> That chafe the spirit, and the mind repress,
> When through the clouds gleam beckoning beyonds
> Where shining vistas mock man's littleness?"
>  - H.P. Lovecraft, "Phaeton" (1918)

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