Posted by Bob Stearns on 10/13/23 11:38

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

[Back to original message]