sql query

This code works perfecty on mysql 4.1 bun my hosting is on mysql4.0.
silly me

Problem is on NOT IN

SELECT hotel.hotel, hotel.hotelid, hotel.price, hotel.star, place.place
FROM hotel left join region on hotel.idregion = region.idregion left
join place on hotel.idplace = place.idplace WHERE hotel.hotelid NOT IN
(select review_item_id from review where review.approve='y') AND
place.idplace = '$idplace' ORDER BY star DESC

How to get it work on mysql 4.0?

What I want.
Give me all hotels from some place (idplace) which are not in table
review or if they are in table review they have "n" in approve field

hotel.hotelid = review.review_item_id

Is it possible and how?

I don't know much about sql and I did this query with lot of help from
forums, documentations and some logic.

txh for your answers

jure

Re: sql query

Jure wrote:
> This code works perfecty on mysql 4.1 bun my hosting is on mysql4.0.
> silly me
>
> Problem is on NOT IN
>
> SELECT hotel.hotel, hotel.hotelid, hotel.price, hotel.star, place.place
> FROM hotel left join region on hotel.idregion = region.idregion left
> join place on hotel.idplace = place.idplace WHERE hotel.hotelid NOT IN
> (select review_item_id from review where review.approve='y') AND
> place.idplace = '$idplace' ORDER BY star DESC

As far as I remember, 4.0 don't support select in select, so the "select
review_item_id from review where review.approve='y'" you need to do first and
then store the values in php and then inject them into the IN().


//Aho

Re: sql query

J.O. Aho wrote:
> Jure wrote:
>
>>This code works perfecty on mysql 4.1 bun my hosting is on mysql4.0.
>>silly me
>>
>>Problem is on NOT IN
>>
>>SELECT hotel.hotel, hotel.hotelid, hotel.price, hotel.star, place.place
>>FROM hotel left join region on hotel.idregion = region.idregion left
>>join place on hotel.idplace = place.idplace WHERE hotel.hotelid NOT IN
>>(select review_item_id from review where review.approve='y') AND
>>place.idplace = '$idplace' ORDER BY star DESC
>
>
> As far as I remember, 4.0 don't support select in select, so the "select
> review_item_id from review where review.approve='y'" you need to do first and
> then store the values in php and then inject them into the IN().
>
>
> //Aho
Without subselect, perhaps a JOIN will do:

SELECT hotel.hotel, hotel.hotelid, hotel.price, hotel.star, place.place
FROM hotel
left join region on hotel.idregion = region.idregion
left join place on hotel.idplace = place.idplace
left join review on hotel.idplace = place.idplace
WHERE place.idplace = '$idplace'
AND (review.approve IS NULL
OR
review.approve<>'y')