Re: Basic Query Question

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)